Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2003
    Location
    Tacoma, WA
    Posts
    4

    Question Unanswered: Query Problem and Anomoly

    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"
    Last edited by rkarman; 12-27-04 at 12:48.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •