mysql UNION重复

Question

I'm using this mysql query and to sort it, I have added bool column is_main (is it from main table). The result is working but I'm getting duplicates, because of that is_main column.. Is there any fix for this? Query:

SELECT 
    b.dateTime,
    a.id,
    a.jobName,
    a.is_main
FROM job_data a, data b
WHERE a.userEmail = b.userEmail AND a.userEmail = :email
UNION
SELECT 
    b.dateTime,
    b.id,
    b.jobName,
    b.is_main
FROM job_data a, data b
WHERE a.userEmail = b.userEmail AND a.userEmail = :email

ORDER BY is_main DESC

The job_data table:

id | dateTime | jobName | is_main
1  | 00.00.00 | job1    | 0
2  | 00.00.00 | job2    | 0
3  | 00.00.00 | job3    | 0

The data table:

id | dateTime | jobName | is_main
1  | 00.00.00 | job1    | 1
2  | 00.00.00 | ---     | 1

The output goes something like this :

job1  //this is not okay, because of duplicate job1
job1
job2 
job3 
_____
---  //this is perfect, because I want empty cell to show on top
job1
job2
job3

Answer 1

SELECT dateTime, id, jobName, max(is_main) from
( SELECT 
    b.dateTime,
    a.id,
    a.jobName,
    a.is_main
FROM job_data a, data b
WHERE a.userEmail = b.userEmail AND a.userEmail = :email
UNION
SELECT 
    b.dateTime,
    b.id,
    b.jobName,
    b.is_main
FROM job_data a, data b
WHERE a.userEmail = b.userEmail AND a.userEmail = :email

)
GROUP BY id