struggling with select query

[lee_m4c]

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.

[Rolando Edwards]

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@...

[Gary Smith-19]

> 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 !!!
>
> 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;
>

Instead of wrapping it twice why not just use () around the or statement.  I do have a question on this.  Isn't using the JOIN statement faster than using a WHERE CLAUSE to join table data?  This would allow him to use a simple OR statement at the end.  You also have locations in there with no reference what so ever.  This would cause redundant work for the SQL engine as it will be seen as a large results set (being result set * number of records in location) prior to being parsed by DISTINCT.

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
)
;

And why not:

SELECT DISTINCT
        TraderPersonalInfo.TraderID,
        PlatformMap.PlatformID
FROM
        TraderPersonalInfo,Locations INNER JOIN PlatformMap
        ON TraderPersonalInfo.TraderID = PlatformMap.TraderID
        INNER JOIN Platforms
        ON PlatformMap.PlatformID = Platforms.PlatformID
WHERE
        PlatformMap.PlatformID = 2 or PlatformMap.PlatformID = 4