SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get items which exists in one table but not other?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maverick_
Posting Yak Master

107 Posts

Posted - 07/04/2013 :  08:49:55  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/04/2013 :  09:21:13  Show Profile  Reply with Quote
Is this what you are looking for:


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);


Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 07/04/2013 :  09:39:14  Show Profile  Reply with Quote
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.

Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/04/2013 :  09:48:29  Show Profile  Reply with Quote
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:

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);

Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 07/04/2013 :  10:26:10  Show Profile  Reply with Quote
Hi Mumu,

Is table 2 where a new record exists, and table1 where its searching for the original items for presence?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/04/2013 :  11:10:12  Show Profile  Reply with Quote
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?

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000