- Items - a list of items, each with an ID.
- People - a list of people, each with an ID.
- Xref - a list of cross references, containing an Item ID, People ID and date.
[web] Retrieve only the latest item (SQL - MySQL)
Author
Suppose you have three tables.
Edit: I misread the problem... Reworking the SQL.
Does the database you're using support sub-queries?
Does the database you're using support sub-queries?
I don't currently have access to a DB. Would something like this work?
SELECT *, MAX(Xref.Date) as limitFROM ItemsLEFT JOIN Xref ON Xref.ItemID = Items.IDWHERE Xref.Date = limit
Author
Quote:Original post by tstrimpThis is MySQL 3.2, so no. There is a page on it here - thanks a lot!
Does the database you're using support sub-queries?
Quote:Original post by benryvesQuote:Original post by tstrimpThis is MySQL 3.2, so no. There is a page on it here - thanks a lot!
Does the database you're using support sub-queries?
This is a fairly tough problem I've had to do before, and the only solutions I've found use subqueries or user defined functions. Doing it in MySQL 3 (which doesn't even know what UNION is!) seems impossible at the face of it, unless you actually build and populate a temporary table through a series of commands.
Author
Quote:Original post by tstrimpUnfortunately not. Unknown column 'limit' in 'where clause'.SELECT *, MAX(Xref.Date) as limitFROM ItemsLEFT JOIN Xref ON Xref.ItemID = Items.IDWHERE Xref.Date = limit
Having the MAX() function in there means that I need to GROUP BY, and I group by Items.ID. This doesn't take the latest date into consideration, so even a HAVING Xref.Date = limit doesn't work.
EDIT: I do realise LIMIT is a keyword, and I didn't use it in the query.
Quote:Original post by Michalson
Doing it in MySQL 3 (which doesn't even know what UNION is!) seems impossible at the face of it, unless you actually build and populate a temporary table through a series of commands.
Does MYSQL 3 understand views?
Edit: Looks like they are new to 5.0.
This can be done in MSSQL
First the XREF sub sub query to get the latest for each item
SELECT ItemID, MAX(RefDate) AS LatestRefDate
FROM Xref
GROUP BY ItemID
Then join to the XREF table again
SELECT ItemID, PeopleID
FROM Xref
INNER JOIN (
SELECT ItemID, MAX(RefDate) AS LatestRefDate
FROM Xref
GROUP BY ItemID
) LatestXref
ON Xref.ItemID = LatestXref.ItemID
AND Xref.RefDate = LatestXref.LatestRefDate
finally LEFT JOIN it to Items
SELECT Items.ItemID, ISNULL(LastPerson.PeopleID, 0) AS LatestPersonID
FROM Items
LEFT OUTER JOIN (
SELECT ItemID, PeopleID
FROM Xref
INNER JOIN (
SELECT ItemID, MAX(RefDate) AS LatestRefDate
FROM Xref
GROUP BY ItemID
) LatestXref
ON Xref.ItemID = LatestXref.ItemID
AND Xref.RefDate = LatestXref.LatestRefDate
) LastPerson
ON Items.ItemID = LastPerson.ItemID
For the above to work I'm assuming that Xrefs primary key is ItemID, PeopleID, RefDate
First the XREF sub sub query to get the latest for each item
SELECT ItemID, MAX(RefDate) AS LatestRefDate
FROM Xref
GROUP BY ItemID
Then join to the XREF table again
SELECT ItemID, PeopleID
FROM Xref
INNER JOIN (
SELECT ItemID, MAX(RefDate) AS LatestRefDate
FROM Xref
GROUP BY ItemID
) LatestXref
ON Xref.ItemID = LatestXref.ItemID
AND Xref.RefDate = LatestXref.LatestRefDate
finally LEFT JOIN it to Items
SELECT Items.ItemID, ISNULL(LastPerson.PeopleID, 0) AS LatestPersonID
FROM Items
LEFT OUTER JOIN (
SELECT ItemID, PeopleID
FROM Xref
INNER JOIN (
SELECT ItemID, MAX(RefDate) AS LatestRefDate
FROM Xref
GROUP BY ItemID
) LatestXref
ON Xref.ItemID = LatestXref.ItemID
AND Xref.RefDate = LatestXref.LatestRefDate
) LastPerson
ON Items.ItemID = LastPerson.ItemID
For the above to work I'm assuming that Xrefs primary key is ItemID, PeopleID, RefDate
select
Items.*,
People.*,
Xref.*
from
Items,
People,
Xref
where
Xref.Item_id = Item.id and
Xref.People_id = People.id
order by
Xref.date desc limit 10
?
Items.*,
People.*,
Xref.*
from
Items,
People,
Xref
where
Xref.Item_id = Item.id and
Xref.People_id = People.id
order by
Xref.date desc limit 10
?
Author
Quote:Original post by Jack9That would not return items that do not have an owner on the cross-reference table.?select Items.*, People.*, Xref.* from Items, People, Xref where Xref.Item_id = Item.id and Xref.People_id = People.id order by Xref.date desc limit 10
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
