Getting Rows From Two Tables from mysql database

Question

0.00/5 (No votes)

See more:

hi all...
i have two tables like one is

uid    name
1      peter
2      parker
3      jhon
4      watson
5      backinsale

and another table like:

uid    date
1      2011-11-26
2      2011-11-26

here i need to get records in which uid is not there in second table that is like

uid    name
3      jhon
4      watson
5      backinsale

i written query like

SQL

select * from tbl1,tbl2 where tbl1.uid!=tbl2.uid

but it is not return correct result set
can any one help me please
thanks in advance...

Posted 25-Nov-11 23:07pm

tulasiram3975

Add a Solution

4 solutions

Solution 4

try this one.

SELECT t2.uid, t2.name FROM t1
RIGHT JOIN t2 on t2.uid = t1.uid
WHERE t2.date IS NOT NULL

Regards,
Eduard

Posted 27-Nov-11 0:42am

Eduard Lu

Updated 27-Nov-11 0:43am

v2

Comments

tulasiram3975 28-Nov-11 2:35am

Thanks For The Help..

[no name] 28-Nov-11 2:41am

welcome

Solution 1

Try using LEFT JOIN[^] like this:

SQL

SELECT *
FROM tbl1
LEFT JOIN tbl2
ON tlb1.uid = tbl2.uid
WHERE tbl2.uid = NULL

OP remarked that the result was empty and he is right the statement should be:

SQL

SELECT tbl1.uid, tbl1.name
FROM tbl1
LEFT JOIN tbl2
ON tbl1.uid = tbl2.uid
WHERE tbl2.uid IS NULL

I also changed the SELECT as otherwise the NULL columns of tbl2 are also shown.

Posted 25-Nov-11 23:19pm

André Kraak

Updated 26-Nov-11 23:00pm

v2

Comments

Mehdi Gholam 26-Nov-11 5:34am

I was going to post the same, 5!

tulasiram3975 26-Nov-11 5:35am

it is also not returning result set it gives me answer as
MySQL returned an empty result set (i.e. zero rows).

André Kraak 27-Nov-11 5:01am

You are right, I found the error and I updated the solution.

tulasiram3975 26-Nov-11 5:37am

It Wont Working i need
uid name
3 jhon
4 watson
5 backinsale
these records.......
thank you

RaisKazi 26-Nov-11 6:23am

Have a look at my answer.

tulasiram3975 28-Nov-11 2:35am

Thanks For The Help..

Solution 2

SELECT dbo.test1.uid, dbo.test1.name
FROM dbo.test1 left JOIN
dbo.test2 ON dbo.test1.uid = dbo.test2.uid
where dbo.test2.uid is null

Posted 26-Nov-11 0:18am

JBSAHU

Comments

tulasiram3975 28-Nov-11 2:35am

Thanks For The Help..

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)

RaisKazi · Accepted Answer · 2011-11-26T00:22:00

Solution 3

Try as below Query.

SQL

Select * from Table1 T1
where T1.uid not in (Select T2.uid from Table2 T2);

Posted 26-Nov-11 0:22am

RaisKazi

Comments

tulasiram3975 28-Nov-11 2:31am

thank you Very Much..

RaisKazi 28-Nov-11 2:32am

Welcome.