CODEWITHSUNDEEP
mysql
JLR
JLR

Reputation: 733

select from two SQL-fields issue

I have an SQL-query like:

SELECT tbl1.alloweduser, tbl2.ID, tbl2.user_login 
FROM tbl1, tbl2 
WHERE NOT tbl1.alloweduser <> tbl2.ID;

The query above compares two fields but outputs the fields that have the same value ("ID" and "alloweduser").

What I would like to do is to output the fields that's NOT matching from tbl2.

The "ID" has the values: 1,2,3,4,5 and the "alloweduser" has values of 1,2,3,4.

I would like to only output the value, 5 and it's corosponding user_login in this example.

How?

Upvotes: 0

Views: 56

Answers (3)

David Chan
David Chan

Reputation: 7505

this is your problem

WHERE NOT tbl1.alloweduser <> tbl2.ID;

<> means "not equals"

you are asking for 

NOT a <> b

which is "NOT NOT equals"

you want 

a <> b

OR 

NOT a = b

NOT both.

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

You probably want to look at using a LEFT JOIN with a null check for this:

SELECT tbl2.ID, tbl2.user_login
FROM tbl2
LEFT OUTER JOIN tbl1
  ON tb2.ID = tb1.alloweduser
WHERE tbl1.alloweduser IS NULL

A great resource for figuring out how to specify joins is linked below:

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Note: For MySQL, you could simply specify LEFT JOIN instead of LEFT OUTER JOIN as they are synonymous.

Upvotes: 1

Gerardo Charles Rojas Vega
Gerardo Charles Rojas Vega

Reputation: 633

Try with:

SELECT tbl2.user_login, tbl2.ID
FROM tbl2 
LEFT JOIN tbl1 ON tbl1.alloweduser = tbl2.ID
WHERE tbl1.alloweduser IS NULL;

Upvotes: 1

Related Questions