Michael Satterwhite said: > On Monday 19 January 2004 13:17, sulewski wrote: >> Okay, I think I'm missing something obvious. I have two tables >> >> Table 1 Table 2 >> ___________ _____________ >> ID rdid vid >> ___________ _____________ >> >> >> ID in table 1 links to rdid in table 2. This is a one to many >> relationship. Now I wish to find all the items in table 1 where >> table2.rdid= table1.rdid and (table2.vid=46) and (table2.vid=554) >> >> In other words which records from table 1 link to two records in >> table 2 who's vid are 46 and 554. >> >> I hope this makes sense. > > Actually, by definition this is an impossible match. The field vid > can only have one value, and you're asking for a match where it has > *TWO* values (46 and 554)
So let's make it 2 fields: SELECT t1.* FROM table1 t1, table2 t2 INNER JOIN table2 t3 ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554) WHERE t1.rdid = t2.rdid Add GROUP BY/DISTINCT per your requirements. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
