I have a query that joins multiple tables with left outer join query from SQL. I want to join a table in such a way that I get only the max id rows being cross matched with one of the other tables. Say I have a table1 that is joined with table 2 and table 3, now comes the 4th table, which also joins the 1st table but in some different way, let me explain it with an image.
The end result that I expect is like the image below
The queries that I have made bring the records when added alone but show an error when left joined with other tables.
Here is the query that brings all the records from tables but I want to get the max record by callid joined with newload id; now it brings the 1st records that are added in newcheckcall table for newload.
$mysqli->query("SELECT *
FROM newload n
LEFT OUTER JOIN truck_details AS t
ON t.truckNumber = n.truck_Number
LEFT OUTER JOIN broker_details AS b
ON b.brokerName = n.Broker
LEFT OUTER JOIN
(select *, MAX(callid) from newcheckcalls GROUP BY newloadID) C ON C.newloadID = n.id
where n.status='load_en_route'
ORDER BY id DESC ")
or die($mysqli->error);
This query brings the records that I expect when queried alone, but when added with the above query, it shows the error.
$mysqli->query("select * from newcheckcalls C
where exists (select 1 from (select newloadID, max(callid) as callid
from newcheckcalls cc
GROUP BY newloadID) as cc
where C.newloadID = cc.newloadID and C.callid = cc.callid)")
or die($mysqli->error);
