sql statement question. Need to query 3 tables in one go!
- by Stefan
Hey there,
I have an sql database. In this database is 3 tables I need to query. The first table has all the item info called item and the other two tables has data for votes and comments called userComment and the third for votes called userItem
I currently have a function which uses this sql query to get the latest more popular (in terms of both votes and comments):
$sql = "SELECT itemID, COUNT(*) AS cnt
FROM (
SELECT `itemID`
FROM `userItem`
WHERE FROM_UNIXTIME( `time` ) >= NOW() - INTERVAL 1 DAY
UNION ALL
SELECT `itemID`
FROM `userComment`
WHERE FROM_UNIXTIME( `time` ) >= NOW() - INTERVAL 1 DAY AND `itemID` > 0
) q
GROUP BY
`itemID`
ORDER BY
cnt DESC";
I know how to change this for either by votes alone or comments....
HOWEVER - I need to query the database to only return the itemID's of the ones which have specific conditions in only the item table these are WHERE categoryID = 'xx' AND typeID = 'xx'
If the sql ninja could please help me on this one? Do I have to first return the results from the above query and the for each in the array fetched then check each against the item table and see if it fits the conditions to build a new array - or is that overkill?
Thanks,
Stefan
