SQL query to implement multiple WHERE condition on same column

Question

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!!!

The query is wrong but he means that he wants the uids that have only 0,2,3,4 values as status. The description is not very clear. Stop downvoting answers if you haven't understand the question.The expected result that is provided is the key to understand what;s exactly the question. — geoandri
– geoandri, Commented Oct 22, 2014 at 19:00
@geoandri I suspect people are downvoting answers because they say Try this followed by code. Those answers are not helpful because they don't explain why the code is useful or what the code does to find the solution. People vote on more than just whether an answer might be right or wrong. — worldofjr
– worldofjr, Commented Oct 22, 2014 at 19:05
That's totally acceptable but most of other answers seem to have missed the actual question. I am not complaining though.Next time i will be much more explanatory. — geoandri
– geoandri, Commented Oct 22, 2014 at 19:10
@geoandri Just edit your answer now to explain why it works. — worldofjr
– worldofjr, Commented Oct 22, 2014 at 19:12

geoandri · Accepted Answer · 2014-10-22 19:15:56Z

3

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);

edited Oct 22, 2014 at 19:15

answered Oct 22, 2014 at 18:47

geoandri

2,4282 gold badges18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

geoandri Over a year ago

To the one who down vote: have you tried it in SQLFIDDLE: sqlfiddle.com/#!2/1a6c20/13

worldofjr Over a year ago

Just wanted to say I hope you've noticed the difference a bit of explanation can have to the usefulness of your answer. Good answer revision! +1 for you.

radar · Accepted Answer · 2014-10-22 19:12:25Z

2

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 )

edited Oct 22, 2014 at 19:12

answered Oct 22, 2014 at 18:48

radar

13.4k2 gold badges27 silver badges33 bronze badges

Comments

Boyd Hemphill · Accepted Answer · 2014-10-22 18:50:30Z

1

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.

answered Oct 22, 2014 at 18:50

Boyd Hemphill

4184 silver badges7 bronze badges

2 Comments

worldofjr Over a year ago

Indeed, AND would have made more sense, although completely not required in this case.

pachabhaiya Over a year ago

The SQL I posted in the description is not the correct one and I'm pretty aware of that. Keeping both AND or OR didn't help me.

worldofjr · Accepted Answer · 2014-10-22 19:35:00Z

1

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.

edited Oct 22, 2014 at 19:35

answered Oct 22, 2014 at 18:44

worldofjr

3,8968 gold badges41 silver badges53 bronze badges

4 Comments

pachabhaiya Over a year ago

The expected result should be WHERE tbl2.status <> 1 plus there should be at least one row with tbl2.status = 0.

worldofjr Over a year ago

@pachabhaiya In which case you need the last of the three options in my answer. tbl2.status = 0 is not required - it doesn't return any more or less results in any case.

pachabhaiya Over a year ago

your logic WHERE tbl2.status = 0 AND tbl2.uid NOT IN (SELECT uid from tbl2 where status=1) works great. Just one question, Is there any performance issue if there are thousands of record with status=1 ?

worldofjr Over a year ago

There's always some sort of performance loss when you add sub-queries, but this is the most efficient way to get the result you want and the difference is very small. This isn't a particularly heavy query anyway.

Edward Manda · Accepted Answer · 2014-10-22 20:11:13Z

0

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;

edited Oct 22, 2014 at 20:11

answered Oct 22, 2014 at 18:45

Edward Manda

5893 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL query to implement multiple WHERE condition on same column

5 Answers 5

2 Comments

Comments

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related