Get distinct records from 2 union sql statement

Question

I would like to seek the advice of some sql experts.

I wrote sql statement for getting authors names from staff and alumni table. Some author's name will be in both tables. So the logic is if the author name is in staff, use that otherwise look for alumni table.

Here is my sql statement, seems fine but it is showing same author name from both staff and alumni table.

SELECT DISTINCT AP.Author_name, P.people_id, P.Name, P.Journal_name, AP.Author_sortorder FROM `Paper_Author` AS AP LEFT JOIN `People` AS P ON ( AP.Author_id = P.people_id ) WHERE AP.Paper_id =3838 UNION SELECT DISTINCT AN.Author_name, N.People_id, N.Name, N.Journal_name, AN.Author_sortorder FROM `Paper_Author` AS AN LEFT JOIN `Alumni` AS N ON ( AN.Author_id = N.People_id ) WHERE AN.Paper_id =3838 ORDER BY Author_sortorder LIMIT 0 , 30

Result:

people_id-- Author_name-- Journal_name-- 1 Name1 A1 2 Name2 B1 3 Name3 C1 3 Name3 C1 4 Name4 D 4 Name4

Expected Result :

 people_id-- Author_name-- Journal_name-- 1 Name1 A1 2 Name2 B1 3 Name3 C1 4 Name4 D

Why does the last record of your current output not have a journal name? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 27, 2017 at 2:49

AlliterativeAlice · Accepted Answer · 2017-12-27 02:52:37Z

This can probably be solved by an additional select using the original result as a subquery

SELECT DISTINCT * FROM ( SELECT DISTINCT AP.Author_name, P.people_id, P.Name, P.Journal_name, AP.Author_sortorder FROM `Paper_Author` AS AP LEFT JOIN `People` AS P ON ( AP.Author_id = P.people_id ) WHERE AP.Paper_id =3838 UNION SELECT DISTINCT AN.Author_name, N.People_id, N.Name, N.Journal_name, AN.Author_sortorder FROM `Paper_Author` AS AN LEFT JOIN `Alumni` AS N ON ( AN.Author_id = N.People_id ) WHERE AN.Paper_id =3838 ORDER BY Author_sortorder LIMIT 0 , 30 );

I think there needs to be an alias on that subselect and it needs to be referenced on the primary statement.
If this answer be correct, then it would imply that the information from the People and Alumni table is always the same for a given person. Possible, but it seems unlikely.

Tim Biegeleisen · Accepted Answer · 2017-12-27 03:49:17Z

The difficulty with this problem is that you might need information from either the People or Alumni tables. We would like to just join to a single table containing the right information. Much of the complexity of the below query is in creating a table which contains the right metadata for each person.

SELECT pa.Author_name, pa.Author_sortorder, t1.people_id, t1.Name, t1.Journal_name FROM Paper_Author pa LEFT JOIN ( SELECT people_id, Name, Journal_name, 0 AS source FROM People UNION ALL SELECT people_id, Name, Journal_name, 1 FROM Alumni ) t1 ON pa.Author_id = t1.people_id INNER JOIN ( SELECT people_id, MIN(source) AS source FROM ( SELECT people_id, 0 AS source FROM People UNION ALL SELECT people_id, 1 FROM Alumni ) t GROUP BY people_id ) t2 ON t1.people_id = t2.people_id AND t1.source = t2.source WHERE pa.Paper_id = 3838;

Collectives™ on Stack Overflow

Get distinct records from 2 union sql statement

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related