CODEWITHSUNDEEP
mysqlsqlduplicatesunion
Kompalisky87
Kompalisky87

Reputation: 31

How can I get non duplicate records in an SQL Union Query?

My SQL UNION command is generating duplicate records. Yes, I know that UNION ALL is suppose to include duplicates while UNION does not... but for some reason, mine is still generating duplicates. I'm not a guru on SQL and know enough to get what I need using simple and basic commands... so I'm sure I have something messed up somewhere.

Goal: To retrieve news articles from an SQL database. I want to grab up to 4 articles with the most relevant articles being first. If there's less than 4 relevant articles (that hasn't expired), I want to fill the remaining with the most recently created articles (that hasn't expired). (An expired articles means some kind of deal has ended so it's no need to show that article to the public).

Current Code (w/ Example):

SELECT *, 0 AS Priority 
FROM tblNews WHERE (ArticleKeywords LIKE '%alaska airlines%')
AND (CURRENT_DATE < ArticleExpireDate OR ArticleExpireDate = '0000-00-00') 
UNION 
SELECT *, 1 AS Priority 
FROM tblNews WHERE (CURRENT_DATE < ArticleExpireDate OR ArticleExpireDate = '0000-00-00') 
ORDER BY Priority, ArticleDate DESC LIMIT 4

This code is currently generating four articles as it's supposed to, but it shows:

  1. ALASKA AIRLINES ARTICLE (relevant article)
  2. ARTICLE 2 (newest article)
  3. ARTICLE 3 (newest article)
  4. ALASKA AIRLINES ARTICLE (newest article)

This happens because a) ALASKA AIRLINES ARTICLE is the only relevant article and b) ALASKA AIRLINES ARTICLE also happens to be one of my most recent articles.

How can I get this UNION to work properly so that it grabs the relevant articles and fills the remaining articles with the newest while not including duplicates that also may be new?

[Sidenote (if it matters): The initial WHERE command can feature multiple LIKE commands if there's more than one keyword. I kept it simple in this command and only showed one keyword. The function of my php code that generates multiple LIKE commands works fine... but I wanted to make this known just in case.]

Upvotes: 0

Views: 394

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Don't use UNION ALL. Use ORDER BY:

SELECT n.*
FROM tblNews n
WHERE CURRENT_DATE < ArticleExpireDate OR
      ArticleExpireDate = '0000-00-00')
ORDER BY (ArticleKeywords LIKE '%alaska airlines%') DESC,
      ArticleDate DESC
LIMIT 4;

Upvotes: 1

GMB
GMB

Reputation: 222432

The additional column in your resultset, called Priority, prevents MySQL to identify the records as duplicates.

I think that you don't need UNION. Conditional ordering should get the job done:

SELECT *
FROM tblNews 
WHERE 
    ArticleExpireDate > CURRENT_DATE  
    OR ArticleExpireDate = '0000-00-00'
ORDER BY
    (ArticleKeywords LIKE '%alaska airlines%') DESC
    ArticleDate DESC
LIMIT 4

Upvotes: 1

Related Questions