CODEWITHSUNDEEP
mysqlsql
Jacob Windsor
Jacob Windsor

Reputation: 6980

SQL - left join SELECT with SELECT COUNT(*)

I need to count the number of rows in track_plays that have a particular track_ID and join this in with my query that selects lots of data from various tables.

Here's what I have so far;

SELECT
    T.ID,
    T.url,
    T.name,
    T.pic,
    T.T_ID,

    COUNT(P.T_ID) AS plays,
    S.Status, 

    G.gig_name,
    G.date_time,
    G.lineup,
    G.price,
    G.ticket,

    E.action,
    E.ID,
    E.timestamp,
    E.E_ID

FROM
    events E
    LEFT JOIN 
    TRACKS T
    ON T.ID = E.ID AND E.action = 'has uploaded a track.' AND E.E_ID = T.T_ID

    LEFT JOIN 
    STATUS S
    ON S.ID = E.ID AND E.action = 'has some news.' AND E.E_ID = S.S_ID

    LEFT JOIN 
    GIGS G
    ON G.ID = E.ID AND E.action = 'has posted a gig.' AND E.E_ID = G.G_ID

    LEFT JOIN 
    track_plays P
    ON P.A_ID = E.ID AND E.action = 'has uploaded a track.' AND E.E_ID = P.T_ID

WHERE E.ID = '3'
ORDER BY E.timestamp DESC LIMIT 15

Hopeflly that should explain what i am trying to do quicker than words can but basically the plays counts the number of plays a track has had. Events is the table where all new ID's go for any new content. They are given an action so we can determine what kind of event they are and therefore retrieve data from the correct table(s).

At the moment, with the COUNT in there the query gives something like this

ID  name  pic  T_ID  plays ...

3    1     2    44    100

The data i have put in there is just representative and it gives more columns than that. The point is though it should give something like this;

ID  name  pic  T_ID  plays ...

3    1     2    44    100

3    3     1    48    10

4    8     2    21    86

As you can see, with the COUNT in there a lot of the data is taken out.

Please comment if you need anymore detail. I don't want to go into too much detail as it may not be needed and i have a tendency to waffle.

Thanks!

Upvotes: 2

Views: 3354

Answers (2)

Jacob Windsor
Jacob Windsor

Reputation: 6980

Done, thanks to everyone who helped. I was missing the GROUP BY clause. Now it works like a charm. Here's the finished product:

SELECT
    T.ID,
    T.url,
    T.name,
    T.pic,
    T.T_ID,

    COUNT(P.T_ID) AS plays,
    S.Status, 

    G.gig_name,
    G.date_time,
    G.lineup,
    G.price,
    G.ticket,

    E.action,
    E.ID,
    E.timestamp,
    E.E_ID

FROM
    events E
    LEFT JOIN TRACKS T
        ON T.ID = E.ID AND E.action = 'has uploaded a track.' AND E.E_ID = T.T_ID
    LEFT JOIN STATUS S
        ON S.ID = E.ID AND E.action = 'has some news.' AND E.E_ID = S.S_ID
    LEFT JOIN GIGS G
        ON G.ID = E.ID AND E.action = 'has posted a gig.' AND E.E_ID = G.G_ID
    LEFT JOIN track_plays P
        ON P.A_ID = E.ID AND E.action = 'has uploaded a track.' AND E.E_ID = P.T_ID
WHERE E.ID = '3'
GROUP BY T.T_ID
ORDER BY E.timestamp DESC LIMIT 15

Upvotes: 2

gillyspy
gillyspy

Reputation: 1598

see this question as you need an explicit group by. I think you need to add this to your query:

...
GROUP BY P.T_ID

Upvotes: 3

Related Questions