Merging 3 tables, display most recently added from one

[Yesideez]

I've got three tables...

 

users

id - INT, primary key

username - VARCHAR

 

cities

id - INT, primary key

city - VARCHAR

There are only 9 entries, one for each city

 

booze

id - INT, primary key

_cityid - links to cities

booze1,booze2,booze3,booze4,booze5,booze6,booze7,booze8 - SMALLINT

added - DATETIME

_userid - links to users

 

This is for an MMORPG crew website I'm putting together to run alongside a game. Members of the crew are updating the game booze prices and instead of simply UPDATEing the booze values more rows are being constantly INSERTed with the cityid and id of the user adding data.

 

What I need to do is to extract (from the booze table) the most recent data, one for each city. For a query which should be simple I've not been getting very far.

 

I've tried something like:

SELECT b.*,c.city,u.username
FROM booze AS b,users AS u,cities AS c
WHERE b._cityid=c.id AND b._userid=u.id
GROUP BY b._cityid
ORDER BY b.added DESC

It sort of works - it does pull only 9 rows of data from booze (1 for each city) but not the most recently added! I've tried LEFT JOIN, RIGHT JOIN and I get the same results.

 

Any assistance on this would be gratefully appreciated.

[kickstart]

Hi

 

I would do something like this

 

SELECT b.*,c.city,u.username
FROM booze AS b
INNER JOIN users AS u
ON b._userid=u.id
INNER JOIN cities AS c
ON b._cityid=c.id 
INNER JOIN (SELECT _cityid, max(id) AS maxId
FROM booze
GROUP BY cityid) z
ON b.id = z.maxId
ORDER BY b.added DESC 

 

Basically join your existing query to a query to get the most recent booze record for each city. This is assuming that the id is in date / time order. If not you could use the max of the added field (but as that could probably be duplicated it is less reliable).

 

All the best

 

Keith

[Yesideez]

Thanks kickstart - this worked an absolute treat!

 

I've got to learn how to run more than one SELECT query like you have. I can see the logic behind it but never would have thought of doing it that way.

 

Would mark as solved but not sure where the button has gone.