SQL Server 2012 :: Join To Find All Records From One Table That Do Not Exist In Other Table
Apr 29, 2014
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
View 7 Replies
ADVERTISEMENT
May 13, 2015
I created a CTE which finds a subset of records from a table
I then ran a SELECT statement against the same table as
SELECT * FROM TABLE
EXCEPT (SELECT * FROM CTE)
Is it possible to add another EXCEPT statement after the CTE EXCEPT statement to cover a condition not incorporated in the CTE definition?
View 9 Replies
View Related
Apr 24, 2014
I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
View 9 Replies
View Related
Jan 7, 2015
I need to find all tables which has a join (either inside an sp, view, etc) with my given table in a db.
sys.dm_sql_referencing_entities doesn't work here.
Note: i dont want to identify by FK References, for 2 reasons:
1) tables might not met with a join (just FK was defined)
2) sometimes, a join happened between tables, without an FK defined
View 1 Replies
View Related
Mar 17, 2008
I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.
Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime
Child Table: TBL_VERIFICATION
PK: Serial Number
Thanks,
Sean
View 1 Replies
View Related
Oct 7, 2015
I am trying to produce a query result that will be using a Case statement to determine values based on scores in a table for each row. The result needs to be exported to be used to upload to a state reporting website. My problem is that the state requires in the CSV file that is uploaded a lot of fields that we do not actually have in the database table we are creating the result set from. After I receive my result set using the Case statement, is there a way to add additional columns that don't actually exist in a table so I can export directly from SQL?
View 6 Replies
View Related
Dec 6, 2013
I know how to check for a sinle vlaue but how do I chekc to see if multiple values exist. I need to check for certain email addresses from a list that I have.
Let us say I ahve 3 email addresses, I want to check for all of them in a table and for eevery email address that is present I want to print something like "You email address is XXX" and if one of those 3 is not found my results should look like
"You email address is XXX"
YYYYY not found
"You email address is ZZZZ"
I'm attaching some TSQL that I tried on [AdventureWorks2012].[Person].[EmailAddress]
/****** Select ALL if where an email address is present in the list ******/
SELECT EmailAddressID,EmailAddress
FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN
(
'ken0@adventure-works.com', --1
'terri0@adventure-works.com', --2
[Code] ....
-- Test to see if a single email address is present
IF EXISTS
(
SELECT EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN ('25rob0@adventure-works.com')
)
BEGIN
SELECT 'Email address is presnt'
[Code] ....
When I check multiples using EXISTS it works as per its design and says YES even if a single item is present.
View 4 Replies
View Related
Jun 13, 2006
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
View 3 Replies
View Related
Jul 24, 2015
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
View 4 Replies
View Related
Jun 20, 2007
I need to find the rows that exist in one table but not in the otherwith this condition:(prod_name exist in table1 and not in table2.prod_name ) AND(prod_name exist in table1 and not in table2.'S'+prod_name )explanation:i want to know if the product not exit and if the combination of thecharachter "S" with the product Name also not exist at the othertableB.Ryuvi
View 2 Replies
View Related
Jan 19, 2014
I have created a table but i forget in which database. I know the table name but i dont know the database name..
I have to filter my table from all database ... how can i do and find....
View 5 Replies
View Related
Oct 10, 2015
i have below queries each select is fetching records at one level. Is there a way i can write single query to get to nth level (recursion) instead joining same table 10 times (i don't know in some cases there is may be next level) I stopped at 10th level now. In below example i gave only two levels.
SELECT Distinct
a.Col1 AS EmpID,
a.Col1 AS EmpID,
a.Col2 AS Emp_guid,
a.Col2 AS Emp_guid,
case
[code].....
View 1 Replies
View Related
Dec 2, 2013
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
View 9 Replies
View Related
Jul 16, 2014
Can't seem to make this SQL query work!
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type
FROM Table1 t1
WHERE t1.Key2 IS NULL
AND t1.Type LIKE 'TypeA'
AND t1.Key1 NOT IN
(SELECT Key1
FROM Table1 t2
WHERE t1.Key1 = t2.Key2
AND t1.Key1 <> t2.Key1
AND t2.Type LIKE 'TypeB')
View 2 Replies
View Related
Nov 7, 2014
I have the following Games table:
CREATE TABLE [dbo].[Games](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Lge] [nvarchar](255) NULL,
[GameDate] [date] NULL,
[HomeTeam] [nvarchar](255) NULL,
[Home_Score] [float] NULL,
[AwayTeam] [nvarchar](255) NULL,
[Away_Score] [float] NULL)
with the following data:
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
[Code] ....
This gives the standings as:
Team B4 - 1 -
Team C1 - 1 1.5
Team A2 - 5 3
How can I query the data to find the "games behind" at any date?
View 9 Replies
View Related
Jan 7, 2014
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
The structure of the periods table is as follows:
[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL
The stored procedure is currently:
USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
View 1 Replies
View Related
Feb 6, 2015
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?
View 6 Replies
View Related
Sep 4, 2014
CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))
The data which will be going into this table is from two table which have a 1 to many relationship:
Here is the 1 side and data:
CREATE TABLE CUSTOMERS(
CUSTOMERNBR VARCHAR(20),
CONTRACT VARCHAR(20),
SUBCONTRACT VARCHAR(20),
STARTDATE DATETIME,
ENDDATE DATETIME DEFAULT '12/31/2099')
[Code] ....
Here is the Many side and data:
CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')
INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')
[Code] ....
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG
10001000101A910400801/01/201401/31/2014
10001000101A910400802/01/201403/21/2014H
10001000101A910400804/01/201404/30/2014
10001000101A910400805/01/201405/31/2014H
10001000101A910400806/01/201412/31/2099E
10001000102A555500101/01/201403/31/2014E
10001000102A555500104/01/201404/30/2014EH
10001000102A555500105/01/201406/30/2014E
10001000102A555500107/01/201412/31/2099
10001000103A666600401/01/201410/01/2014
View 1 Replies
View Related
Feb 24, 2015
I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now.
ResumeSkill Skill
Id SkillId
SkillId Name
I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.
View 2 Replies
View Related
Dec 19, 2013
My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
View 9 Replies
View Related
Oct 22, 2014
I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.
CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)
[Code] .....
The results I am looking to achieve are:
PLanIDGroupIDSubGroupIDAddOnCodeExternalCode
101000005LAA1M231_1
101000005OCA2M231_2
101000005UCA3M231_3
101000005XCA4M231_4
201000005LAA1M231_1
201000005OCA2M231_2
201000005UCA3M231_3
201000005XCA4M231_4
Is there an SQL statement that can be used to accomplish this?
View 1 Replies
View Related
Mar 28, 2015
Our system runs a SQL Server 2012 DB, it has a table (table_a) which has over 10M records. Our system have to receive data file from previous system daily which contains approximate 3M updated or new records for table_a. My job is to update table_a with the new data.
The initial solution is:
1 Create a table (table_b) which structur is as the same as table_a
2 Use BCP to import updated records into table_b
3 Remove outdated data in table_a:
delete from table_a inner join table_b on table_a.key_fileds = table_b.key_fields
4 Append updated or new data into table_a:
insert into table_a select * from table_b
As the test result, this solution is very inefficient. Step 3 costs several hours, e.g. How can I improve it?
View 9 Replies
View Related
Aug 11, 2015
Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
View 9 Replies
View Related
May 29, 2008
'****************************************************************************
Cmd.CommandText = "Drop Table Raj"
Cmd.ExecuteNonQuery()
Cmd.CommandText = "Select * Into Raj From XXX"
Cmd.ExecuteNonQuery()
'**************************************************************************
This generates error that Table already exist.
If Wait 1 sec then execute statement then it works fine.
Thanks in Advance
Piyush Verma
View 1 Replies
View Related
Oct 5, 2007
Hello friends,
I have a one problem, i have a table in that some reocrds are duplicate.i want to find which records are duplicate.
for exp. my table is as follows
emp_id emp_name
1 aa
2 bb
3 cc
1 aa
3 cc
3 cc
and i want the result is like
emp_id emp_name
1 aa
1 aa
3 cc
3 cc
3 cc
View 6 Replies
View Related
Mar 5, 2015
I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.
Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.
View 2 Replies
View Related
May 5, 2015
I'm trying to run an Insert on tables using a User Login but I keep getting the error above. I ran the insert using my own credentials and it worked. The User LOGIN in question has read & write permission to the database.
View 4 Replies
View Related
Feb 4, 2015
Is there any possibility to trace, most used column in every table on the SQL server?
The intention is, a query which suggests some columns to be indexed; Based on the usage of the column by the queries that run on server.
View 4 Replies
View Related
Oct 12, 2015
I have a database of 900+ tables with around 3000 SPs, and views. Manually I reviewed few tables and found that tables are not referenced with FK and I applied few. There are lots of tables and SPs using them in join statement, Is there any way with which I can get each tables missing references, any DMV or other manual script which tells about this?
View 2 Replies
View Related
Oct 20, 2015
Is there a way to find all the table names that have a foreign key that references FILE_ID_IN([FILE_ID])
Note: The table def below has a foreign key
IF object_id('CODE_MAP_IN', 'U') IS NOT NULL DROP TABLE CODE_MAP_IN
Print 'Creating table CODE_MAP_IN...'
Create table CODE_MAP_IN
(
[CODE_MAP_FR] [varchar] (53),
[CODE_MAP_TO] [varchar] (53),
[FILE_ID] [float] DEFAULT 1000
PRIMARY KEY ([CODE_MAP_FR]),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]
GO
View 2 Replies
View Related
Jul 15, 2014
I have table A
|account | Unmort |
| A |10.000.000 |
and a Table B
|account| Jenis | Nominal | Unmort |Total|
-------------------------------------------
| A | 021 | 200.000| - | - |
| A | 028 | 3.200.000| - | - |
| A | 023 | 7.200.000| - | - |
how to update to be like this??
|account| Jenis |Nominal | Unmort |Total |
| A | 021 |200.000 | - |200.000 |
| A | 028 |3.200.000 | 2.800.000 |400.000 |
| A | 023 |7.200.000 | 7.200.000 | 0 |
for this type of account number jenis 021 Field Unmort Fill set= 0 and Field Total must not be a minus...
View 3 Replies
View Related
Oct 21, 2014
I have 2 tables that I need to merge let me explain. I have a range of product ID's that have a product grouping of * meaning all product groups. So I have a table with products and one with around 100 groups
ProdID ProdGrp
-------- ---------
11 *
12 *
ProdGrp ProdGrpDesc
--------- ---------------
A Prod Group A
B Prod Group B
C Prod Group C
I need a table which looks like the below but I have no joining mechanism
ProdID ProdGrp
-------- ---------
11 A
11 B
11 C
12 A
12 B
12 C
View 3 Replies
View Related
Jun 9, 2015
I have standard SQL query for clients, but sometimes a client does not have a specific table.
How do I deal with that? (creating the table is not an option)
SELECT
A.Col1
,A.Col2
,B.Col3
--> Col 50
FROM
TableA A Join
TableB B ON A.ColA = B.ColA
Suppose in some cases TabelB does not exist. How do I make sure the rest of the query runs okay and does not result in an error?
View 4 Replies
View Related
