i need an assistance on some sql query

Question

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 30-Oct-11 10:08am

sivakumarmr10

Add a Solution

4 solutions

Solution 2

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]

Posted 30-Oct-11 10:38am

Maciej Los

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?

Solution 4

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

Posted 30-Oct-11 20:53pm

Amir Mahfoozi

Comments

[no name] 31-Oct-11 2:55am

thankq for u replay

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

André Kraak · Accepted Answer · 2011-10-30T10:29:00

Solution 1

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]

Posted 30-Oct-11 10:29am

André Kraak

Updated 31-Oct-11 9:06am

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.

Monjurul Habib 31-Oct-11 16:23pm

my 5!

André Kraak 31-Oct-11 16:25pm

Thanks.

Prasanta_Prince · Accepted Answer · 2011-10-30T18:02:00

Solution 3

select table1.pid,table1.pcode from table1 where pid not in (select pid from table2)

I think it can help you.

Posted 30-Oct-11 18:02pm

Prasanta_Prince