Re: struggling with select query

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" <lee@...>
To: mysql@...
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/mysql?unsub=redwards@...



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...