Mysql UNION ALL creates double records

Question

I have the following tables:

platforms:

-------------------------------------------------------
| id | team_id| name| url | logo
-------------------------------------------------------

job_platforms:

-------------------------------------------------------
| job_id | platform_id | startdate | enddate 
-------------------------------------------------------

I would like to create a list of all the platforms linked to a specific job with and without the startdate and enddate

-------------------------------------------------------
| id | url| logo| startdate | enddate 
-------------------------------------------------------

But i am stuck at this point. When a startdate and/or an enddate has been created, the platform can be found in the job_platforms. But when the platform has not been selected for the current job, the platform will not be saved in job_platforms. Only platform_id's with a startdate and/or enddate will be stored in job_platforms

At first i was thinking of storing all the platforms whether a platform is activated or not (startdate and/or enddate). If a platform does not have a startdate and/or an enddate, the fields would be null.

But this will not work when new platforms will be added on the platform list. So i was thinking about using a UNION and it gets me close to where i wanna be, but not quiet yet :)

So this is what i have at the moment:

SELECT DISTINCT
 platforms.id,
 platforms.url,
 platforms.logo,
 platforms.no_platform_site,
 job_platforms.startdate as startdate,
 job_platforms.enddate as enddate,
 platforms.team_id
FROM
 platforms
LEFT JOIN job_platforms ON job_platforms.platform_id = 
 platforms.id
WHERE 
 platforms.team_id IN (1,2,3,4,5)
AND 
 job_platforms.job_id = 30

UNION ALL
SELECT DISTINCT
 platforms.id,
 platforms.url,
 platforms.logo,
 platforms.no_platform_site,
 null as startdate,
 null as enddate,
 platforms.team_id
FROM
 platforms
LEFT JOIN job_platforms ON job_platforms.platform_id = 
 platforms.id
WHERE 
 platforms.team_id IN (1,2,3,4,5)

What happens with this query is that i get all the right platforms that are linked to job_id 30, but when a startdate is not empty the record comes up twice. 1 time with the empty date and 1 time with the startdate.

I uploaded a picture of the result set here

How can i change my query so that the double records will not be shown?

Thanks in advance!

score 3 · Accepted Answer · answered Feb 01 '19 at 15:00

I think you just want a left join:

SELECT DISTINCT p.id, p.url, p.logo, ps.no_platform_site,
       jp.startdate as startdate, jp.enddate as enddate,
       p.team_id
FROM platforms p LEFT JOIN
     job_platforms jp
     ON jp.platform_id = p.id AND jp.job_id = 30
WHERE p.team_id IN (1, 2, 3, 4, 5);

Notes:

The important part is moving jp.job_id = 30 into the ON clause. In the WHERE clause it turns the outer join into an inner join.
I introduced table aliases, so the query is easier to write and to read.
I doubt you really need SELECT DISTINCT. If not, remove it. It only adds overhead.

thank you so much, this is exactly what i was looking for :D — user2314339, Feb 01 '19 at 15:33

Mysql UNION ALL creates double records

1 Answers1