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
– John Parker, Commented Apr 2, 2011 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
– John Parker, Commented Apr 2, 2011 at 10:51
you didn't clarified. mark baker answered this question as it is — dynamic
– dynamic, Commented Apr 2, 2011 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
– Mark Baker, Commented Apr 2, 2011 at 10:53

RichardTheKiwi · Accepted Answer · 2011-04-02 11:01:38Z

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 2, 2011 at 11:01

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

7 Comments

Anonymous1 Over a year ago

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 )

RichardTheKiwi Over a year ago

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

Anonymous1 Over a year ago

You're right -- using "table" in the example was not the smartest thing to do. Your answer works great. Thanks!

Martin Smith Over a year ago

Ah I see. I think = and DISTINCT still might perform better than IN from this OP's experience?

RichardTheKiwi Over a year ago

@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 :)

|

Andrew Jackman · Accepted Answer · 2011-04-02 10:58:16Z

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 2, 2011 at 10:58

Andrew Jackman

14k8 gold badges38 silver badges44 bronze badges

1 Comment

Anonymous1 Over a year ago

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

Collectives™ on Stack Overflow

Conditional MySQL Query

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related