Hello all! I hope you had a very merry Christmas. OK here is the situation...

I have three tables. One contains areas. One contains area types. And another links the area with a group. The concept is to return all records in the area table and only the group IDs (GID) form the linking table that match an area and are limited to specific group. I would think that this could be accomplished with outer joins up to the point that I want to limit the linking return to a specific GID. So to recap I want to return all areas and only those areas that are in the linking table for a specific group.

I have tried UNIONs but since the two records are not exactly the same they repeat:


SELECT
`areas`.`AreaID`,
`areas`.`Area`,
`areatypes`.`description`,
`areatypes`.`category`,
NULL AS `GID`
FROM
`areas`
LEFT OUTER JOIN `areatypes` ON (`areas`.`type` = `areatypes`.`type`)

UNION

SELECT
`areas`.`AreaID`,
`areas`.`Area`,
`areatypes`.`description`,
`areatypes`.`category`,
`areaslt`.`GID`
FROM
`areaslt`
LEFT OUTER JOIN `areas` ON (`areaslt`.`AreaID` = `areas`.`AreaID`)
LEFT OUTER JOIN `areatypes` ON (`areas`.`type` = `areatypes`.`type`)
WHERE
(`areaslt`.`GID` = 1)

ORDER BY
`category`,
`Area`


I have tried JOINs but when I limit it to a specific GID I only get those records.


SELECT
`areatypes`.`description`,
`areatypes`.`category`,
`groupinfo`.`Area`,
`groupinfo`.`AreaID`,
`areaslt`.`GID`
FROM
`areatypes`
LEFT OUTER JOIN `areas` `groupinfo` ON (`areatypes`.`type` = `groupinfo`.`type`)
LEFT OUTER JOIN `areaslt` ON (`groupinfo`.`AreaID` = `areaslt`.`AreaID`)
WHERE
`areaslt`.`GID` = 1
ORDER BY
`areatypes`.`category`,
`areatypes`.`type`,
`groupinfo`.`Area`


The closest I have gotten is using a sub query but this is where it really get's interesting...


SELECT
`areatypes`.`description`,
`areatypes`.`category`,
`groupinfo`.`Area`,
`groupinfo`.`AreaID`,
(SELECT `areaslt`.`GID` FROM `areaslt` WHERE `groupinfo`.`AreaID`= `areaslt`.`AreaID` AND `areaslt`.`GID` = 1) AS `groupID`
FROM
`areatypes`
LEFT OUTER JOIN `areas` `groupinfo` ON (`areatypes`.`type` = `groupinfo`.`type`)


This seems to work right up until the time I try to ORDER BY.

SELECT
`areatypes`.`description`,
`areatypes`.`category`,
`groupinfo`.`Area`,
`groupinfo`.`AreaID`,
(SELECT `areaslt`.`GID` FROM `areaslt` WHERE `groupinfo`.`AreaID`= `areaslt`.`AreaID` AND `areaslt`.`GID` = 1) AS `groupID`
FROM
`areatypes`
LEFT OUTER JOIN `areas` `groupinfo` ON (`areatypes`.`type` = `groupinfo`.`type`)
ORDER BY
`areatypes`.`category`,
`areatypes`.`description`,
`groupinfo`.`Area`


When I add the ORDER BY caluse it marks all the areas as group 1. WHY?????

I don't expect you to take my word for this, because I certainly wouldn't, so I will try and include the tables below. But for you information I am using MySQL 5.0.0-alpha with MyISAM tables on a Win2k server. Any idea are greatly appriciated...

Areas table
AreaID,Area,type
1,"276-Appalachia",3
313,"Stafford Co.",1
278,"Nelson Co.",1
3,"540-Shenandoah",3
4,"804-Central",3
5,"757-Tidewater",3
295,"Prince George",2
312,"Spotsylvania Co.",1
314,"Staunton",2


Areas Linking Table
AreaID,GID
314,2
295,2
314,1
312,1


Area Types
type,description,category
1,"Counties","Areas Served"
2,"Cities","Areas Served"
3,"State Regions","Areas Served"