CODEWITHSUNDEEP
mysql
Saymon
Saymon

Reputation: 520

MySQL UNION query return duplicate values even using GROUP BY

I have the following query:

SELECT *
FROM (
    SELECT 
        m.id AS id,
        reference_id, 
        title, 
        created_by, 
        publish_up, 
        state 
    FROM z_news_master m  
    LEFT JOIN z_news_english c ON m.id = c.reference_id
    WHERE c.created_by = 17152
    ORDER by c.id DESC
) AS A

UNION

SELECT * FROM (
    SELECT 
        m.id AS id, 
        reference_id,
        title, 
        created_by, 
        publish_up, 
        state 
    FROM z_news_master m  
    LEFT JOIN z_news_spanish c ON m.id = c.reference_id
    WHERE c.created_by = 17152
    ORDER by c.id DESC 
) AS B
GROUP BY id

Basically, I have 3 tables (z_news_master, z_news_english, z_news_spanish), to store News in Spanish or English languages. The z_news_master table contains the generic news information, the z_news_english and z_news_spanish contain the news in its respective language.

I need to get a list of the news, if the news is in both language tables it should return only one (not duplicated), the code above does the work, but if there is a new in English and Spanish, the record gets duplicated.

I'd also like to know why the GROUP BY id and the GROUP BY reference_id don't work?

Upvotes: 0

Views: 45

Answers (2)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

You can do this without using a union. The statement below gets all rows from the master table, and join any existing rows from both the spanish and english tables. If a row exists in the spanish table, it uses the values from that table. If a row exists in the english table, and not the spanish table, it uses the values from that table. If no matching row exists in either the english or spanish table, it returns columns from the master table.

You can alter the priorities by changing the order of the WHEN's.

SELECT 
    CASE 
        WHEN NOT s.id IS NULL THEN s.id 
        WHEN NOT e.id IS NULL THEN e.id 
        ELSE m.id AS `id`, 
    CASE 
        WHEN NOT s.reference_id IS NULL THEN s.reference_id 
        WHEN NOT e.reference_id IS NULL THEN e.reference_id 
        ELSE m.reference_id AS `reference_id`, 
    CASE 
        WHEN NOT s.title IS NULL THEN s.title 
        WHEN NOT e.title IS NULL THEN e.title 
        ELSE m.title AS `title`, 
    CASE 
        WHEN NOT s.created_by IS NULL THEN s.created_by 
        WHEN NOT e.created_by IS NULL THEN e.created_by 
        ELSE m.created_by AS `created_by`, 
    CASE 
        WHEN NOT s.publish_up IS NULL THEN s.publish_up 
        WHEN NOT e.publish_up IS NULL THEN e.publish_up 
        ELSE m.publish_up AS `publish_up`, 
    CASE 
        WHEN NOT s.state IS NULL THEN s.state 
        WHEN NOT e.state IS NULL THEN e.state 
        ELSE m.state AS `state`
FROM z_news_master m  
LEFT JOIN z_news_spanish s ON m.id = s.reference_id
LEFT JOIN z_news_english e ON m.id = e.reference_id
WHERE m.created_by = 17152
ORDER by m.id DESC 
GROUP BY m.id

EDIT

Per Paul Spiegel's comment here's an even shorter version:

SELECT 
    COALESCE(s.id, e.id, m.id) AS `id`, 
    COALESCE(s.reference_id, e.reference_id, m.reference_id) AS `reference_id`, 
    COALESCE(s.title, e.title, m.title) AS `title`, 
    COALESCE(s.created_by, e.created_by, m.created_by) AS `created_by`, 
    COALESCE(s.publish_up, e.publish_up, m.publish_up) AS `publish_up`, 
    COALESCE(s.state, e.state, m.state) AS `state`
FROM z_news_master m  
LEFT JOIN z_news_spanish s ON m.id = s.reference_id
LEFT JOIN z_news_english e ON m.id = e.reference_id
WHERE m.created_by = 17152
ORDER by m.id DESC 
GROUP BY m.id

Upvotes: 0

Paul Spiegel
Paul Spiegel

Reputation: 31772

Use a NOT EXISTS subquery to remove a fallback language row if a corresponding row for the prefered language exists. Assuming the prefered language is "english", the query would be:

SELECT 
    m.id AS id,
    reference_id, 
    title, 
    created_by, 
    publish_up, 
    state 
FROM z_news_master m  
JOIN z_news_english c ON m.id = c.reference_id
WHERE c.created_by = 17152

UNION ALL

SELECT 
    m.id AS id, 
    reference_id,
    title, 
    created_by, 
    publish_up, 
    state 
FROM z_news_master m  
JOIN z_news_spanish c ON m.id = c.reference_id
WHERE c.created_by = 17152
  AND NOT EXISTS (
    SELECT *
    FROM z_news_english e
    WHERE e.reference_id = m.id
      AND e.created_by = c.created_by
  )

ORDER by id DESC

Note that there is no need for GROUP BY. And a LEFT JOIN doesn't make sense because you have a WHERE condition on a column from the right table (which would convert the LEFT JOIN to INNER JOIN).

Upvotes: 1

Related Questions