I'm trying to write a SELECT statement for mysql which basically works like this:
SELECT * FROM Table1, Table2
WHERE
Table1.Field1 = Table2.Field1 AND
<<if Table2.Field2 = x>>
Table2.Field2 = x AND Table2.Field3 = m
<<else if Table2.Field2 = y>>
Table2.Field2 = y AND Table2.Field4 = n
<<end if>>
Obviously the syntax inside the <<double brackets>> isn't correct, it's just a representation of what I would like to accomplish.
I only want records from Table1 which are attached to two Table2 records (one Table2 record with an 'xm' combination, AND a different Table2 record with a 'ym' combo). In other words, I only want records like this:
Table1
Record1
ID_Field = 223
Table2
Record1
ID_Field = 223
Field2 = x
Field3 = m
Field4 = null
Record2
ID_Field = 223
Field2 = y
Field3 = null
Field4 = n
I tried the following:
SELECT * FROM Table1, Table2
WHERE Table1.Field1 = Table2.Field1
AND(
(Table2.Field2 = x AND Table2.Field3 = m)
OR
(Table2.Field2 = y AND Table2 .Field4 = n)
)
But I get more records than I want, because it returns records with the 'xm' combo even if the 'yn' combo isn't there, and it returns records with the 'yn' combo even if the 'xm' combo isn't there.
When I replace 'OR' in the above query with 'AND', then I get no results whatsoever, I think for obvious reasons. I have also tried using a LEFT JOIN, but I get the same results as when I use 'OR' in the sample query.
Any help would be appreciated. Thanks!
