sql statement question. Need to query 3 tables in one go!

Posted by Stefan on Stack Overflow See other posts from Stack Overflow or by Stefan
Published on 2010-05-02T22:35:49Z Indexed on 2010/05/02 22:48 UTC
Read the original article Hit count: 242

Filed under:
|
|
|
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql