Click here to Skip to main content
15,922,155 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi

i am dealing with two table just like table1 and table2

in two table pid is the Common thing for ex

table1==>
pid , pcode
1000 , jk1
1001 , jk2
1002 , jk3
1003 , jk4
1004 , jk5
1005 , jk6
table2==>
pid , status
1001 , completed
1005 , completed

output==>
pid , pname
1000 , jk1
1002 , jk3
1003 , jk4
1004 , jk5


<pre>note: here for visualization i had saparated the two columns by comms</pre>

for ex these two are the tables so that i should get the data from the table1 but we should consider the table2 data;
i need the output table as output table which was specified in above
it mean that table2 contains the pid should not come in the output table

i am thinking but iam not able to write this thing so please

can any one tell me the query for this Operation
thanks in advanced
Posted

You can try
SQL
SELECT *
FROM table1 AS T1
LEFT JOIN table2 AS T2 ON T2.pid = T1.pid
WHERE T2.pid IS NULL

or
SQL
SELECT *
FROM table1 AS T1
WHERE NOT EXISTS (SELECT * FROM table2 AS T2 WHERE T2.pid = T1.pid)


This works when table2 only contains information about records you want you leave out of the result.

See Join Fundamentals[^] to read about JOIN and EXISTS (Transact-SQL)[^] for the EXISTS command.


[edit]Changed "FROM table AS t1" to "FROM table1 AS t1" - OriginalGriff[/edit]
 
Share this answer
 
v5
Comments
OriginalGriff 30-Oct-11 16:40pm    
Fixed minor typo in second option.
(Your first example doesn't work - it returns an empty list)
[no name] 31-Oct-11 2:54am    
thankq for u replay
André Kraak 31-Oct-11 15:07pm    
Your right of course, it should be T2.pid IS NULL
OriginalGriff 31-Oct-11 15:13pm    
:thumbsup:
André Kraak 31-Oct-11 15:16pm    
Thanks.
select table1.pid,table1.pcode from table1 where pid not in (select pid from table2)


I think it can help you.
 
Share this answer
 
Example 1 - get all pid's from table2 where status is completed:
SQL
SELECT t1.[pid], t1.[pcode] AS [pname]
FROM table2 AS t2 LEFT JOIN table1 AS t1 ON t1.[pid] = t2.[pid]
WHERE t2.[status] = 'completed'


Example 2 - get all pid's from table1 no matter of status:
SQL
SELECT t1.[pid], t1.[pcode] AS [pname], t2.[status]
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.[pid] = t2.[pid]
 
Share this answer
 
Comments
Maciej Los 30-Oct-11 16:40pm    
When i wrote my solution i haven't seen the answer of Andre Kraak
[no name] 31-Oct-11 2:54am    
thankq for u replay
Maciej Los 31-Oct-11 18:03pm    
You welcome ;) If my post was helpful, can you rate it?
Here is another solution in addition to others :

SQL
select * from table1 where pid not in (
     select pid from table2 where status = 'completed'
)


Hope it helps
 
Share this answer
 
Comments
[no name] 31-Oct-11 2:55am    
thankq for u replay

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