D Durham D Durham - 4 months ago 10
MySQL Question

Conflicting where clause in MYSQL query

I have 3 tables, one with 'papers' (P), one with 'authors' (A) and one with 'authors__papers' (AP) which is a lookup table with a few other values as well. What I need is a single query that gives me one column with the author name where the 'is_contact_author' field in AP = 0, and another column with all the author names (concatenated) where AP.author_order > 0. These should be grouped by P.paper_id.

Each query by itself looks like:

SELECT CONCAT(A.first_name,' ',A.last_name) as contact FROM authors A
join authors__papers AP on AP.author_id = A.author_id
WHERE AP.is_contact_author = 0


And:

SELECT AP.paper_id, GROUP_CONCAT(CONCAT(A.first_name,' ',A.last_name) ORDER BY AP.author_order SEPARATOR ', ') as authors FROM authors A
join authors__papers AP on AP.author_id = A.author_id
WHERE AP.author_order > 0
GROUP BY AP.paper_id


Each of those returns the expected separate result. I tried a subquery:

SELECT * FROM
(
SELECT CONCAT(A.first_name,' ',A.last_name) as contact FROM authors A
join authors__papers AP on AP.author_id = A.author_id
WHERE AP.is_contact_author = 1
) contact,

(
SELECT AP.paper_id, GROUP_CONCAT(CONCAT(A.first_name,' ',A.last_name) ORDER BY AP.author_order SEPARATOR ', ') as authors FROM authors A
join authors__papers AP on AP.author_id = A.author_id
WHERE AP.author_order > 0
GROUP BY AP.paper_id
) author_info


But that hangs (or maybe is just REALLY slow). Any ideas how best to get this output from both queries in one result set? Every paper has one contact and at least one author. The author_order for contacts is always 0 and authors is 1 through X. The is_contact_author for contacts is always 1 and for authors is always 0 if that helps or matters.

Answer

Of course it slow, there is not join condition so you are doing a Cartesian join.

Try this:

SELECT * FROM 
(
    SELECT AP.paper_id,CONCAT(A.first_name,' ',A.last_name) as contact FROM authors A
    join authors__papers AP on AP.author_id = A.author_id 
    WHERE  AP.is_contact_author = 1
) contact
INNER JOIN
(
    SELECT AP.paper_id, GROUP_CONCAT(CONCAT(A.first_name,' ',A.last_name) ORDER BY AP.author_order SEPARATOR ', ') as authors
    FROM authors A
    join authors__papers AP on AP.author_id = A.author_id 
    WHERE  AP.author_order > 0
    GROUP BY AP.paper_id
) author_info
ON(contact.paper_id = author_info.paper_id)

Try to use the proper syntax of joins, this will help you avoid this mistakes.

Comments