Conditional MySQL Query

Question

I have a table with columns: conditional1, conditional2, data. I need to return all the rows where conditional1 = value1 if any of those rows also contains conditional2 = value2. How can I do this?

Edit: There's some confusion about what I'm asking.

If you have columns

`conditional1 | conditional2 | data

A | A | A

A | B | A`

I want to return both rows if conditional1 = A and conditional2 = B

Edit 2: There's still some confusion.

Here is another example:

conditional1 | conditional2 | data

1 | 1 | A

1 | 2 | B

2 | 1 | C

2 | 2 | D

If conditional1 = 1 and conditional2 = 1, it should return

1 | 1 | A

1 | 2 | B

If conditional1 = 2 and conditional2 = 1, it should return

2 | 1 | C

2 | 2 | D

If conditional1 = 2 and conditional2 = 2, it should return

2 | 1 | C

2 | 2 | D

If conditional1 = 2 and conditional2 = 3, it should return no rows.

Are you stuck with the query itself, or implementing this in PHP, or ? — John Parker, Apr 02 '11 at 10:41
You question still makes no sense. (Perhaps that's why *all* of the existing answers are incorrect.) Do you mean if conditional1 = A **OR** conditional2 = B? — John Parker, Apr 02 '11 at 10:51
you didn't clarified. mark baker answered this question as it is — dynamic, Apr 02 '11 at 10:51
What condition determines whether you return the first row? With your edit, this is very unclear... what links the first row to the second to say that it should be returned even though conditional2 is not 'B'? — Mark Baker, Apr 02 '11 at 10:53
middaparka: No. In the example, all rows where conditional1 = A are returned, but only if conditional2 = B for some row where conditional1 = A. The current answers below would only return where both conditionals are true, which is one row and not two. — Anonymous1, Apr 02 '11 at 10:55
You newest example wasn't very good... maybe you should have used different letters in the third column to help quickly differentiate the difference for your new cases — Andrew Jackman, Apr 02 '11 at 11:01
I modified the example accordingly. I can make another example if that would help. — Anonymous1, Apr 02 '11 at 11:03

score 3 · Accepted Answer · answered Apr 02 '11 at 11:01

3

select *
from tbl A
where conditional1 = 'A'
and exists (
  select * from tbl B
  where B.conditional1 = 'A' and B.conditional2 = 'B')

or a more MySQL friendly version

select * from tbl
where conditional1 in
(
    select conditional1 
    from tbl
    where conditional1 = 'A' and conditional2 = 'B'
)

answered Apr 02 '11 at 11:01

RichardTheKiwi

105,798
26
196
262

I tried your second version, but I'm getting syntax errors with it:select * from table where conditional1 in ( select conditional1 from table where conditional1 = 1 and conditional2 = 1 ) – Anonymous1 Apr 02 '11 at 11:20
If you're using the literal "table", then that's obviously wrong. Otherwise I syntax checked in MySQL and SQL Server at least, both are fine with it – RichardTheKiwi Apr 02 '11 at 11:24
You're right -- using "table" in the example was not the smartest thing to do. Your answer works great. Thanks! – Anonymous1 Apr 02 '11 at 11:29
Are you sure that's a MySQL friendly answer? I thought MySQL [had big problems with subqueries contained in `in`](http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql/3417190#3417190)? i.e. that it re-evaluates the sub query for each row in the outer query. – Martin Smith Apr 02 '11 at 11:46
@Martin / comparing the two, the IN clause can only ever return one value for conditional1 (I could have made it 'A'). The first one uses EXISTS, which has some weird optimization (I know because it performs worse than LEFT JOIN + IS NULL) - so I took the safer route.. – RichardTheKiwi Apr 02 '11 at 11:50
1

Ah I see. I think `=` and `DISTINCT` still might perform better than `IN` from [this OP's experience](http://stackoverflow.com/questions/3416076/this-select-query-takes-180-seconds-to-finish)? – Martin Smith Apr 02 '11 at 11:53
1

@Martin / ok got you now. I should have used '='. But this is a very simple query, so the bug won't bite? I tend to leave unedited answers alone, since it looks .. clean :) – RichardTheKiwi Apr 02 '11 at 11:55

score 0 · Answer 2 · answered Apr 02 '11 at 10:58

0

SELECT * FROM table 
    WHERE conditional1 = 'A' AND 1 IN 
        ( SELECT 1 FROM table WHERE conditional1 = 'A' AND conditional2 = 'B' LIMIT 1 )

I think I finally understand!

answered Apr 02 '11 at 10:58

Andrew Jackman

13,781
7
35
44

Sorry, there's a problem on my end: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Anonymous1 Apr 02 '11 at 11:27

Conditional MySQL Query

2 Answers2