November 1, 2007 at 5:51 pm
Hi Guys
I have got two tables - a) Master Table b) StartDate Table
Now this start date table is as follows
Client No: Start date
1 1/1/2003
2 2/2/2006
3 11/12/2001
This tabel has got unique client nos with the start date of each client.
Now this master table has got huge no. of client nos along with their start dates.
Client no Start date
1 1/1/2003
2 2/2/2003
...
..
...
Now I need to find those client nos which are there in the master table but not in the startdate table
How do I do that??
Thanks
November 2, 2007 at 1:16 am
Hi
You must do a left join and pull out all the records where there is no match. When you select form Master table and do a left join on StartDate table, then you will get NULLs for all those where there was no match
Like this:
SELECT * FROM Master M LEFT JOIN StartDate S
ON M.ClientNo = S.ClientNo
WHERE S.ClientNo IS NULL
November 2, 2007 at 1:46 pm
Another option is to use a NOT EXISTS.
SELECT * FROM Master M
WHERE NOT EXISTS (SELECT 1 FROM StartDate S
WHERE M.ClientNo = S.ClientNo)
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply