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

You need to post more information about the structure of each table. How does records in the ITEM table relate to records in the USER table? — Declan_K
– Declan_K, Commented Sep 5, 2013 at 17:02

Declan_K · Accepted Answer · 2013-09-05 17:17:07Z

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

edited Sep 5, 2013 at 17:17

answered Sep 5, 2013 at 17:06

Declan_K

6,8162 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jonnny Over a year ago

Just going through it, it only returns the count for ID #13 nothing for Id #15

Declan_K Over a year ago

What result does the following give you? SELECT COUNT(I.id) FROM ITEM I WHERE I.user_id = 15 - My guess is that user 15 has no items. If you need users without items included change the INNER JOIN to a LEFT OUTER JOIN.

Jonnny Over a year ago

It returns 7. Which is right, they both have records in there.

Jonnny Over a year ago

I just put a GROUP BY u.id at the end of the statement and it now returns 2 rows with the id, expiry and item count. Thanks again

Jojje · Accepted Answer · 2013-09-05 17:11:02Z

0

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.

answered Sep 5, 2013 at 17:11

Jojje

3814 silver badges14 bronze badges

Collectives™ on Stack Overflow

Mysql Subquery with join

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related