MySQL break out group clause from subquery

Posted by Anton Gildebrand on Stack Overflow See other posts from Stack Overflow or by Anton Gildebrand
Published on 2012-11-21T10:42:01Z Indexed on 2012/11/21 11:00 UTC
Read the original article Hit count: 237

Filed under:
|

Here is my query

SELECT 
    COALESCE(js.name,'Lead saknas'),
    count(j.id)
FROM 
    jobs j
LEFT JOIN
    job_sources js
    ON j.job_source=js.id
LEFT JOIN 
    (SELECT 
        * 
    FROM 
        quotes 
    GROUP BY 
        job_id) q 
    ON j.id=q.job_id
GROUP BY
    j.job_source

The problem is that it's allowed for each job to have more than one quote. Because of that i group the quotes by job_id. Now sure, this works. But i don't like the solution with a subquery. How can i break out the group clause from the subquery to the main query? I have tried to add q.job_id to the main group clause, both before and after the existing one but don't get the same results.

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysqli