SQL Left Join Issues

[mb89]

Hi,

I don't understand joins etc as well as I should as I have gotten a bit stuck now.

I have a table of items which have been tested multiple times on different test reports. I have to select all of the items to ensure I only get one entry but I then need to sort by the report date to get information about the report from the most recent occurance of the item. Basically its something like this:


SELECT
item.*
FROM item
LEFT JOIN test_report_items ON test_report_items.ID = item.itemID
LEFT JOIN test_report ON test_report.ID = test_report_items.test_reportID
WHERE
item.companyID = '4'
ORDER BY
test_report.date DESC,
item.productID


So the problem with this though is when we do the first left join, there will be multiple matching records to join to but I need the most recent. How can I tell a left join to do this?

Any advice would be appreciated.

[Fumigator]

I'm not sure why you're LEFT JOINing but not selecting any data from the joined table. A LEFT join will return a row from the item table whether there's a match or not, so the join does nothing for you.

I'd do a subquery (well, a double-subquery), selecting the row that matches the item_id whose report_id matches the first report_id found when sorted by date desc. I just confused myself and I don't actually know if this would work! :eek:


SELECT item.*
FROM item
WHERE itemID = (
SELECT test_report_items.ID
FROM test_report_items
WHERE test_report_items.test_reportID = (
SELECT test_report.ID
FROM test_report
ORDER BY test_report.date DESC
LIMIT 1)
)
AND item.companyID = '4'


I doubt the ORDER BY and LIMIT works inside the subquery actually.... so I'll keep looking for a solution.

[Fumigator]

Actually... I think it worked with the addition of a WHERE condition on the first subquery....


SELECT item.*
FROM item
WHERE itemID = (
SELECT test_report_items.ID
FROM test_report_items
WHERE item.itemID = test_report_items.ID
AND test_report_items.test_reportID = (
SELECT test_report.ID
FROM test_report
ORDER BY test_report.date DESC
LIMIT 1)
)
AND item.companyID = '4'


So looking at it, the first subquery is really the same as a normal JOIN (not a LEFT join) so you could probably do a normal JOIN on test_report_items, and a subquery on test_report to get the most recent report.

[mb89]

Thank you!

I've never seen that type of MySQL syntax before. I don't do much MySQL as my databases are generally just content management so I should probably go back and do some more studying up!

Thanks again!