Transcendence Transcendence - 7 days ago 7
MySQL Question

MySQL PDO Subquery construct help - Never done this before

I am trying to run a sub query in MySQL, however I cant seem to get this to work. Ploughed through many posts trying to find a silution. It's all a little over my head. Im hoping this is a simple one for you guys.

SELECT * FROM jobs x
LEFT JOIN status b on x.id = b.job_id
LEFT JOIN delivery d on x.id = d.job_id
LEFT JOIN logos h on x.id = h.job_id
LEFT JOIN customers c on x.customer_id = c.customer_id
LEFT JOIN (SELECT *
FROM communicator t
WHERE x.id = t.job_id
GROUP BY t.id
DESC
LIMIT 1)
WHERE x.status=1


So the problem is the last
LEFT JOIN
where I start a sub query. I cant get it to work. What am I doing wrong? I only want one result from the communicator table the last record added, hence the order by
DESC
limit 1.

Answer

You are confusing the 2 normally used types of sub query. When doing a join against a sub query you need to give the sub query an alias name. Further when doing a join against a sub query you wouldn't reference a field from another table in that sub query - rather you would reference such a table in the ON clause.

Something like this I would expect:-

SELECT * 
FROM jobs x 
LEFT JOIN status b on x.id = b.job_id 
LEFT JOIN delivery d on x.id = d.job_id 
LEFT JOIN logos h on x.id = h.job_id 
LEFT JOIN customers c on x.customer_id = c.customer_id
LEFT JOIN 
(
    SELECT * 
    FROM communicator t 
    GROUP BY t.id 
) sub0
ON x.id = sub0.job_id
WHERE x.status=1

However a further issue is that your sub query is doing a GROUP BY t.id, but you are returning the job_id and other fields. Which particular job_id is returned is for an id not defined. How your multiple fields relate and which ones you need I do not know, but I suspect for a start what you would want is something like this, to get the latest id field for a job_id

SELECT * 
FROM jobs x 
LEFT JOIN status b on x.id = b.job_id 
LEFT JOIN delivery d on x.id = d.job_id 
LEFT JOIN logos h on x.id = h.job_id 
LEFT JOIN customers c on x.customer_id = c.customer_id
LEFT JOIN 
(
    SELECT job_id, MAX(id) AS id
    FROM communicator  
    GROUP BY job_id
) sub0
ON x.id = sub0.job_id
WHERE x.status=1