[SOLVED] Bringing back the right results with LEFT JOIN

[elgoog]

I am hoping this makes sense once i write it down... here goes...

 

table1

---------------

id      name

1      one

2      two

3      three

 

table2

------------

id        type

1          1

1          2

2          1

3          2

Bringing back distinct records with a type of 1

SELECT Distinct(id), name, type FROM table1 LEFT JOIN table2 ON table2.id = table1.id WHERE table2.type = 1

 

brings back......

 

id     name      type

1      one          1

2      two          1

 

 

This is ok..However.... i now want to bring back the distinct id's that have types of both 1 and 2

 

SELECT Distinct(id) FROM table1 LEFT JOIN table2 ON table2.id = table1.id WHERE table2.type = 1 AND table2.type = 2

 

This brings back 0 records

 

 

using OR brings back records with either

SELECT Distinct(id) FROM table1 LEFT JOIN table2 ON table2.id = table1.id WHERE table2.type = 1 OR table2.type = 2

 

id     name

1      one   

2      two

3      three

 

 

i am looking to only return the result

 

id     name

1      one   

 

as this is the only one who has a  joined record with both 1 and 2

 

 

 

 

Thanks in Advance.

 

[kickstart]

Hi

 

Your trouble is that you are trying to check to returned rows at once. You really need both matches returned on one row.

 

An example way to do it (and just using a normal join rather than an outer join).

 

SELECT Distinct(a.id)

FROM table1 a

JOIN table2 b ON b.id = a.id AND b.type = 1

JOIN table2 c ON c.id = a.id AND c.type = 2

 

All the best

 

Keith

[elgoog]

Cheers Keith... Have it all sorted and working using this method.