Fi Horan Fi Horan - 2 years ago 53
SQL Question

Selecting two rows in a single sql statement only if both conditions are met

I've got a table with the following data.

table name: myTable

prodID catNo variable1 variable2
1 20 Cat Blue
2 10 Cat Red
2 15 Cat Green
2 20 Cat Black
3 20 Cat Yellow
4 10 Cat Orange
4 15 Cat Brown
4 20 Cat Black
5 30 Cat Pink

I want to be able to select all columns from myTable where the following is true
"(prodID = 2 and catNo = 10) AND (prodID = 2 and catNo = 15)". Therefore getting a result of the two rows only if both conditions are met and it will return nothing if both rows aren't present.
So my results table will look like this.

table name: results

prodID catNo variable1 variable2
2 10 Cat Red
2 15 Cat Green

I've tried to use conditional if statements but can't seem to get them working in the sql. My current solution it to get back all rows with prodID = 2 and then using php to do the if statement to decide what to display but this won't work with the pagination I've designed for displaying the results as my limit will distort the number of results per page.
I know I could use 'having count rows=2' but I'm not sure how to word it.

Answer Source

Thank you M Khalid Junaid's answer works. I've managed to come up with a solution that doesn't require a join which with a very large table like mine will be it would be rather slow:

select * 
from myTable 
where ((prodID = 2 and catNo = 10) or (prodID = 2 and catNo = 15)) 
group by catNo
having (
        select count(distinct(catNo))
        from myTable 
        where ((prodID = 2 and catNo = 10) or (prodID = 2 and catNo = 15))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download