Click here to Skip to main content
15,881,938 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
SELECT     SampleRegistration.SampleNumber
FROM         SampleRegistration INNER JOIN
                      NewSampleEntrys ON SampleRegistration.QlCode = NewSampleEntrys.QLID INNER JOIN
                      Protocol ON SampleRegistration.SampleNumber = Protocol.SampleNumber
WHERE     (NewSampleEntrys.RecvdDate > '1/1/2013') AND (SampleRegistration.Tested = 'F')


In above Query i want all matching record from NewSampleEntrys & SampleRegistration and skip all record in Protocol table.

what changes should i made plz help.
Posted
Updated 5-Sep-14 3:22am
v2

You can try using NOT IN operator..

SQL
SELECT     SampleRegistration.SampleNumber
FROM         SampleRegistration INNER JOIN
                      NewSampleEntrys ON SampleRegistration.QlCode = NewSampleEntrys.QLID
WHERE     (NewSampleEntrys.RecvdDate > '1/1/2013') AND (SampleRegistration.Tested = 'F') AND SampleRegistration.SampleNumber Not IN (SELECT SampleNumber From Protocol)
 
Share this answer
 
If you want to skip records in Protocol that do not have a match in SampleRegistration, you should use a LEFT JOIN:
SQL
SELECT       SampleRegistration.SampleNumber
FROM         SampleRegistration 
INNER JOIN   NewSampleEntrys ON SampleRegistration.QlCode = NewSampleEntrys.QLID 
LEFT JOIN    Protocol ON SampleRegistration.SampleNumber = Protocol.SampleNumber
WHERE        (NewSampleEntrys.RecvdDate > '1/1/2013') 
AND          (SampleRegistration.Tested = 'F')
 
Share this answer
 
Comments
Nikhil Bhivgade 5-Sep-14 8:59am    
i think i am not able to put my question properly.

if samplenumber is present in protocal table it should not been displayed in result
Solution 2 works, but is slow. See http://www.componentworkshop.com/blog/2009/06/26/sql-server-basics-avoiding-in-and-not-in[^] for more information on why you don't want queries with IN or NOT IN. You can keep your original join, but add a condition that no record in Protocol was found by checking for null values:
SQL
SELECT       SampleRegistration.SampleNumber
FROM         SampleRegistration
INNER JOIN   NewSampleEntrys ON SampleRegistration.QlCode = NewSampleEntrys.QLID
LEFT JOIN    Protocol ON SampleRegistration.SampleNumber = Protocol.SampleNumber
WHERE        (NewSampleEntrys.RecvdDate > '1/1/2013')
AND          (SampleRegistration.Tested = 'F')
AND          (Protocol.Id IS NULL)

Assuming Id is an existing Id column in the Protocol table. If it's named differently, please update the condition to reflect this.
 
Share this answer
 
Hi Nikhil,
Please check this

SQL
SELECT  SampleRegistration.SampleNumber
FROM    SampleRegistration
INNER JOIN NewSampleEntrys ON SampleRegistration.QlCode = NewSampleEntrys.QLID
Where SampleRegistration.SampleNumber not in (Select SampleNumber from Protocol)
and (NewSampleEntrys.RecvdDate > '1/1/2013') AND (SampleRegistration.Tested = 'F')


It may help for you, good luck,
 
Share this answer
 
Comments
kbrandwijk 5-Sep-14 17:29pm    
How is this different from Solution 2?
Manikandan MAC 6-Sep-14 2:10am    
Hi, I am sorry, I didn't see solution 2, both are same.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900