CODEWITHSUNDEEP
mysqlsql
pachabhaiya
pachabhaiya

Reputation: 75

SQL query to implement multiple WHERE condition on same column

I have two tables, tbl1 and tbl2 as below:

CREATE TABLE tbl1 (`uid` int);

INSERT INTO tbl1 (`uid`)
VALUES
    (100),
    (200),
    (300),
    (400);

CREATE TABLE tbl2 (`id` int, `uid` int, `status` int);

INSERT INTO tbl2 (`id`, `uid`, `status`)
VALUES
    (1, 100, 0),
    (2, 100, 1),
    (3, 100, 2),
    (4, 100, 4),
    (5, 200, 0),
    (6, 200, 1),
    (7, 300, 0),
    (8, 300, 3),
    (9, 300, 4),
    (10, 400, 1),
    (11, 400, 2);

SQLFIDDLE: http://sqlfiddle.com/#!2/1a6c20/13

Problem: I want to join these two tables. The result should show the rows having tbl2.status = 0 but not having tbl2.status = 1.

This is the SQL query which I'm trying to run:

SELECT DISTINCT tbl1.uid, tbl2.id, tbl2.status 
FROM tbl1
INNER JOIN tbl2 ON (tbl1.uid = tbl2.uid)
WHERE tbl2.status = 0
OR tbl2.status <> 1;

CORRECT expected result is: 7, 300, 0.

Here, uid=300 has a row with status=0 and this uid=0 has no row with status=1. So this is the expected result that I want.

uid=100 has both status=0 and status=1, so this is not the required result.
uid=200 also has both status=0 and status=1 so this is not the required result.
uid=400 does not have status=0, this is not the required result.

Help please!!!

Upvotes: 2

Views: 229

Answers (5)

Edward Manda
Edward Manda

Reputation: 579

Putting OR doesn't make sense, just remove the OR part and Try this:

SELECT DISTINCT tbl1.uid, tbl2.id, tbl2.status 
FROM tbl1
INNER JOIN tbl2 ON (tbl1.uid = tbl2.uid)
WHERE status = 0;

Upvotes: 0

worldofjr
worldofjr

Reputation: 3886

Remove

OR tbl2.status <> 1

It is illogical (if the column equals 0 then it can't equal 1) and confuses the query.

If you want all unique uids where status equals 0, but never equals 1, then use a subquery, with the AND logic;

WHERE tbl2.status = 0
AND tbl2.uid NOT IN (SELECT uid FROM tbl2 WHERE status=1)

This selects all the rows where status equals 0, and then removes the rows where the same uid has a status that equals 1. This will give you the expected result you gave in the question.

If you want all rows except those where status equals 1, use;

WHERE tbl2.status <> 1

Which gives you exactly the same result as the current query, the tbl2.status = 0 is irrelevant.

Upvotes: 1

geoandri
geoandri

Reputation: 2428

You need to select uids with status=0 but not the ones that appear in your table also with status=1. So you need to exclude them from the result set. You need one more condition in your where clause to have the expected result. This can be done by using NOT IN.

Try the following query

SELECT  tbl1.uid, tbl2.id, tbl2.status 
 FROM tbl1
INNER JOIN tbl2 ON (tbl1.uid = tbl2.uid)
WHERE tbl2.status = 0
and tbl2.uid NOT IN (SELECT uid from tbl2 where status=1);

Upvotes: 3

radar
radar

Reputation: 13425

you can use NOT EXISTS clause

SELECT DISTINCT T1.uid, T2.id, T2.status 
FROM tbl1 T1
INNER JOIN tbl2 T2 ON (T1.uid = T2.uid)
WHERE T2.status = 0
AND NOT EXISTS ( SELECT 1 FROM tbl2 T22
                 where T2.uid = T22.uid
                 and T22.status =1 )

Upvotes: 2

Boyd Hemphill
Boyd Hemphill

Reputation: 418

There is a logic error with the OR in the WHERE clause.

The clause WHERE tbl2.status = 0 produces the desired result:

(7, 300, 0)

The clause OR tbl2.status <> 1 produces 

(7, 300, 0)
(8, 300, 3)
(9, 300, 4)

Since this is an OR the union is taken, and you get all three tuples.

People new to SQL often find OR to be tricky. I used to keep truth tables near me when an unexpected result confused me.

Upvotes: 1

Related Questions