JBone JBone - 1 year ago 73
MySQL Question

Nested Select statement in MYSQL join

SELECT * FROM A
JOIN B
ON B.ID = A.ID
AND B.Time = (SELECT max(Time)
FROM B B2
WHERE B2.ID = B.ID)


I am trying to join these two tables in MYSQL. Don't pay attention to that if the ID is unique then I wouldn't be trying to do this. I condensed the real solution to paint a simplified picture. I am trying to grab and join the table B on the max date for a certain record. This procedure is getting run by an SSIS package and is saying B2.ID is an unknown column. I do things like this frequently in MSSQL and am new to MYSQL. Anyone have any pointers or ideas?

Answer Source

I do this type of query differently, with an exclusion join instead of a subquery. You want to find the rows of B which have the max Time for a given ID; in other words, where no other row has a greater Time and the same ID.

SELECT A.*, B.*
FROM A JOIN B ON B.ID = A.ID
LEFT OUTER JOIN B AS B2 ON B.ID = B2.ID AND B.Time < B2.Time
WHERE B2.ID IS NULL

You can also use a derived table, which should perform better than using a correlated subquery.

SELECT A.*, B.*
FROM A JOIN B ON B.ID = A.ID
JOIN (SELECT ID, MAX(Time) AS Time FROM B GROUP BY ID) AS B2
  ON (B.ID, B.Time) = (B2.ID, B2.Time)

P.S.: I've added the greatest-n-per-group tag. This type of SQL question comes up every week on Stack Overflow, so you can follow that tag to see dozens of similar questions and their answers.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download