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.Pla tformID
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" <lee@lmac.co.uk>
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-wit....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/mysql?unsub=redwards@swmx.com