> 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
