Mysql Subquery with join

Question

I have quite a complicated query (for me) that I'm not sure how to execute, although I have tried. I have a

user table

license table FK user.id is found in license.parent_id and license.child_id

item table FK is user.id is found in item.user_id

I need to count the number of items for each user in the item table that belong to a user and their licensees where the license hasn't expired.

This is as far as I've got with it so far.

SELECT *
FROM
(SELECT id as user_id, date_expired as user_date_expired 
FROM user 
WHERE id IN (13, 15)
) AS user
JOIN
(
SELECT COUNT(id) as item_count FROM item WHERE date_added > DATE_SUB(NOW(), INTERVAL     300 DAY) AND date_expired > CURDATE() 
) AS item

currently returns:

user_id user_date_expired item_count

13 2013-10-05 20:23:31 24

15 2013-08-08 22:21:09 24

Appreciate any help.

Jonny

Answer 1

It appears from the information that you have provided that the license table is not required.

Here is the query rewritten with the correct join structure between the user and item tables.

SELECT  U.ID            AS user_id
        ,U.date_expired AS user_date_expired 
        ,COUNT(I.id)    AS item_count 
FROM    USER U
INNER JOIN
        ITEM I
ON      I.user_id = U.id
WHERE   U.id IN (13,15)
AND     I.date_added > DATE_SUB(NOW(), INTERVAL  300 DAY) 
AND     I.date_expired > CURDATE()     
GROUP BY
        U.ID
        ,U.date_expired

Answer 2

Skip the subqueries and use plain join for all three tables, and then add a group by clause for all columns that should be equal on each result row.