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

Question

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

Answer 1

It's not clear to me exactly how your DB is setup or where some of these attributes are but it might be something like:

$sql = "SELECT  q.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 inner join item i on q.itemID = i.itemID
WHERE i.categoryID = 'xx' AND i.typeID = 'xx'
GROUP BY
        q.`itemID`
ORDER BY
        cnt DESC";

Answer 2

I would be inclined to include the filter on each of the queries. I would need to verify but I would think that it would perform better as fewer rows are analyzed in the Group By. Thus, I would do something like (this is a guess since we are not told how the item table relates to the others):

$sql = "Select  itemID, Count(*) AS cnt
From    (
        Select ui.`itemID`
        From `userItem` ui
            Join `item` i
                On i.itemid = ui.itemid
        Where From_UnixTime( `time` ) >= Now() - Interval 1 Day
            And i.categoryID = 'xx'
            And i.typeid = 'xx'
        Union All
        Select uc.`itemID`
        From `userComment` uc
            Join `item` i
                On i.itemid= uc.itemid
        Where From_UnixTime( `time` ) >= Now() - Interval 1 Day
            And uc.itemID > 0
            And i.categoryID = 'xx'
            And i.typeid = 'xx'
        )  q
Group By `itemID`
Order By cnt Desc";