You need to embed the PlatformMap and Platforms tables twice Use PM2 as the PlateformMap where PlatformID=2 Use PF2 for PlatformID check of 2 Use PM4 as the PlateformMap where PlatformID=4 Use PF4 for PlatformID check of 4 Join PM2 and PM4 where TraderIDs are equal
select distinct TraderPersonalInfo.TraderID,PM2.PlatformID,PM4.PlatformID from TraderPersonalInfo,Locations,Platforms PF2,Platforms PF4, PlatformMap PM2,PlatformMap PM4 where (TraderPersonalInfo.TraderID = PM2.TraderID) and (PM2.PlatformID = PF2.PlatformID) and PM2.PlatformID = 2 and (TraderPersonalInfo.TraderID = PM4.TraderID) and (PM4.PlatformID = PF4.PlatformID) and PM4.PlatformID = 4 and PM2.TraderID=Pm4.TraderID; Give it a try !!! ----- Original Message ----- From: "lee_m4c" <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Tuesday, February 27, 2007 7:55:20 AM (GMT-0500) Auto-Detected Subject: struggling with select query Guys, Below is a select query which I'm stuggling with, so I'd be grateful for any help you could give me. select distinct TraderPersonalInfo.TraderID,PlatformMap.PlatformID from TraderPersonalInfo,Locations,PlatformMap,Platforms where (TraderPersonalInfo.TraderID = PlatformMap.TraderID) and (PlatformMap.PlatformID = Platforms.PlatformID) and PlatformMap.PlatformID = 2 or PlatformMap.PlatformID = 4; The query above produces the following output: +----------+------------+ | TraderID | PlatformID | +----------+------------+ | 5 | 4 | | 4 | 4 | | 1 | 4 | | 2 | 4 | | 3 | 4 | | 6 | 4 | | 7 | 4 | | 9 | 4 | | 10 | 4 | | 11 | 4 | | 20 | 4 | | 13 | 4 | | 14 | 4 | | 15 | 4 | | 19 | 4 | | 17 | 4 | | 18 | 4 | | 23 | 4 | | 22 | 4 | | 24 | 4 | | 2 | 2 | | 5 | 2 | | 6 | 2 | | 7 | 2 | | 9 | 2 | | 10 | 2 | | 11 | 2 | | 22 | 2 | +----------+------------+ but, I only want to know which traders have platformID 2 *and* platformiID4. In this case, only traderID 5 has both. I can't seem to work out how to structure the statement so it works as a *AND*, not *OR* as above. Any help would be greatly appreciated. Cheers. - Lee. -- View this message in context: http://www.nabble.com/struggling-with-select-query-tf3300643.html#a9181415 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
