MYSQL JOIN SELECT Statment - omit duplicated

Question

Hi,

I am tying to join the following 2 queries but I am having duplicated .... it is possible to remove duplacted fro this:

(
SELECT bar_id, bar_name, town_name, bar_telephone, 
        (subscription_type_id *2) AS subscription_type_id
FROM bar, sportactivitybar, towns, subscriptiontype
WHERE sport_activity_id_fk =14
    AND bar_id = bar_id_fk
    AND town_id = town_id_fk
    AND subscription_type_id = subscription_type_id_fk
)
UNION 
(
SELECT bar_id, bar_name, town_name, bar_telephone, 
         subscription_type_id
FROM bar, towns, subscriptiontype
WHERE town_id = town_id_fk
    AND subscription_type_id = subscription_type_id_fk
)
ORDER BY subscription_type_id DESC , RAND( )

Please note that I need to omit those duplicates that will have a lower subscription_type_id

Answer 1

The fastest way would be to use a temp table. And them from there you could do insert the first query into the temp table and then only insert those rows you want from the second query that aren't in the temp table either by using an outer join to the temp table or by using a not in statement. Or you could insert all of the second query and just use a group by clause in your select from the temp table.

Answer 2

If I understand you correctly, a simple GROUP BY, witholding only the maxium subscription types should do the trick.

SELECT  dupAlias.bar_id
        , dupAlias.bar_name
        , dupAlias.town_name
        , dupAlias.bar_telephone
        , MAX(dupAlias.subscription_type_id) AS subscription_type_id
FROM    (
          SELECT  bar_id
                  , bar_name
                  , town_name
                  , bar_telephone
                  , (subscription_type_id *2) AS subscription_type_id
          FROM    bar
                  , sportactivitybar
                  , towns
                  , subscriptiontype
          WHERE   sport_activity_id_fk =14
                  AND bar_id = bar_id_fk
                  AND town_id = town_id_fk
                  AND subscription_type_id = subscription_type_id_fk
          UNION 
          SELECT  bar_id
                  , bar_name
                  , town_name
                  , bar_telephone
                  , subscription_type_id
          FROM    bar
                  , towns
                  , subscriptiontype
          WHERE   town_id = town_id_fk
                  AND subscription_type_id = subscription_type_id_fk
        ) dupAlias
GROUP BY
        dupAlias.bar_id, dupAlias.bar_name, dupAlias.town_name, dupAlias.bar_telephone
ORDER BY 
        dupAlias.subscription_type_id DESC , RAND( )

Answer 3

You can bracket your query:

SELECT bar_id, bar_name, town_name, bar_telephone, 
    max(subscription_type_id)
FROM
(
    (
    SELECT bar_id, bar_name, town_name, bar_telephone, 
            (subscription_type_id *2) AS subscription_type_id
    FROM bar, sportactivitybar, towns, subscriptiontype
    WHERE sport_activity_id_fk =14
        AND bar_id = bar_id_fk
        AND town_id = town_id_fk
        AND subscription_type_id = subscription_type_id_fk
    )
    UNION 
    (
    SELECT bar_id, bar_name, town_name, bar_telephone, 
             subscription_type_id
    FROM bar, towns, subscriptiontype
    WHERE town_id = town_id_fk
        AND subscription_type_id = subscription_type_id_fk
    )
) x
GROUP BY bar_id, bar_name, town_name, bar_telephone
ORDER BY subscription_type_id DESC , RAND( )