Get items which exists in one table but not other? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get items which exists in one table but not other?

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-07-04 : 08:49:55

Hi guys,

I am trying to write a report which searches for a new item existing in one table but is not present in another.

In my mind it sounds really simple because its a comparison but for some reason I have a SQL writers block and don't know how!

Request table is where a new item could be entered.
Item table is where original items records are kept.

Each items has a unique code which is identified by Item_Code (e.g. ABC1234, DDD122, etc) field which exists in both.

The join between the two tables is:
Contract_Code
Order_Number
Job_Number

Does anyone know how to write an SQL for this kind of query? :-)

If it helps, each Item that gets added is given a unique Item_Number (e.g. 1, 2, 3, 4) if four items are added.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-04 : 09:21:13

Is this what you are looking for:
[CODE]

DECLARE @Table1 TABLE (ID INT, Code VARCHAR(10), ONumber INT, JNumber INT);

INSERT INTO @Table1 VALUES
(1, 'ABC1234', 1000, 77),
(2, 'DDD122', 1001, 79),
(3, 'ABC1235', 1002, 80),
(4, 'DDD1221', 1003, 77);

DECLARE @Table2 TABLE (ID INT, Code VARCHAR(10), ONumber INT, JNumber INT);
INSERT INTO @Table2 VALUES
(1, 'ABC1234', 1000, 77),
(2, 'DDD122', 1001, 79),
(6, 'ABC1235', 1004, 80),
(7, 'DDD123', 1005, 80);

SELECT * FROM @Table2 T1 WHERE
NOT EXISTS (SELECT 1 FROM @Table1 T2
WHERE T1.Code = T2.Code and
T1.ONumber = T2.ONumber and
T1.JNumber = T2.JNumber);

[/CODE]

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-07-04 : 09:39:14

Hi Mumu,

I can only write 'read-only' SQL queries. I.e. No Insert Into or Declare.

The tables already exists with records so I want to search the Request table and see if it contains new items which did not exist in Item table.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-04 : 09:48:29

quote:
Originally posted by Maverick_

Hi Mumu,

I can only write 'read-only' SQL queries. I.e. No Insert Into or Declare.

The tables already exists with records so I want to search the Request table and see if it contains new items which did not exist in Item table.

I gave you example tables and how to get the results you wanted.
you have insert your table names and associate column names in the following query:
[CODE]
SELECT * FROM @Table2 T1 WHERE
NOT EXISTS (SELECT 1 FROM @Table1 T2
WHERE T1.Code = T2.Code and
T1.ONumber = T2.ONumber and
T1.JNumber = T2.JNumber);

[/CODE]

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-07-04 : 10:26:10

Hi Mumu,

Is table 2 where a new record exists, and table1 where its searching for the original items for presence?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-04 : 11:10:12

Yes

quote:
Originally posted by Maverick_

Hi Mumu,

Is table 2 where a new record exists, and table1 where its searching for the original items for presence?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources