#1
  1. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785

    Need to return 3 result sets at once using union


    Hello -

    My objective is to use union to combine the results of these three queries into a single result set. I need a set with reviewsEnabled =1 and then the second where reviewEnabled = 0 but total reviews > 0 and then the rest.

    I am getting an error though "every derived table must have its own alias" - I tried adding aliases to the selects but with no luck. Any suggestions?

    Code:
    SELECT * FROM (
                   (SELECT iShopID, (3959 * acos (cos (radians (33.869706)) * cos (radians (fLatitude)) *
                    cos (radians (fLongitude) - radians (-117.741241)) +
                    sin (radians (33.869706)) * sin (radians (fLatitude)))) AS fDistanceMiles, 1 as sortOrder
                   FROM Shop
                   WHERE iReviewsEnabledFlag = 1
                   HAVING fDistanceMiles < 50
                   ORDER BY iReviewsEnabledFlag DESC, fDistanceMiles ASC) as S1
                   UNION
                   (SELECT iShopID, (3959 * acos (cos (radians (33.869706)) * cos (radians (fLatitude)) *
                    cos (radians (fLongitude) - radians (-117.741241)) +
                    sin (radians (33.869706)) * sin (radians (fLatitude)))) AS fDistanceMiles,
                    (SELECT COUNT(*) FROM Review WHERE iShopID = Shop.iShopID) as reviewCount,
                    2 as sortOrder
                   FROM Shop
                   WHERE iReviewsEnabledFlag = 0
                   AND reviewCount > 0
                   HAVING fDistanceMiles < 50
                   ORDER BY reviewCount DESC, fDistanceMiles ASC) as S2
                   UNION
                   (SELECT iShopID, (3959 * acos (cos (radians (33.869706)) * cos (radians (fLatitude)) *
                    cos (radians (fLongitude) - radians (-117.741241)) +
                    sin (radians (33.869706)) * sin (radians (fLatitude)))) AS fDistanceMiles,
                    (SELECT COUNT(*) FROM Review WHERE iShopID = Shop.iShopID) as reviewCount,
                    3 as sortOrder
                   FROM Shop
                   WHERE iReviewsEnabledFlag = 0
                   AND reviewCount = 0
                   HAVING fDistanceMiles < 50
                   ORDER BY fDistanceMiles ASC) as S3
    )
    ORDER BY sortOrder
    LIMIT 100;
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    try

    Code:
                 ...........
                   HAVING fDistanceMiles < 50
                    ORDER BY fDistanceMiles ASC) as S3 
    )  as DT
     ORDER BY sortOrder 
    LIMIT 100
    ;

    Comments on this post

    • Matt1776 agrees
  4. #3
  5. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785
    That worked! Thank you!
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded

IMN logo majestic logo threadwatch logo seochat tools logo