CODEWITHSUNDEEP
mysqlcountgroup-byleft-joinwhere-clause
lickmycode
lickmycode

Reputation: 2079

MySQL COUNT in LEFT JOIN WHERE

I'm not an expert, keep this in mind please when answering my question... I've read many other examples about here on stackoverflow, but I simply don't understand how to use it with my task.

I got an Album database table, and a Photo database table:

table_album:

album_id   album_creatoruser_id
-------------------------------
   1           777
   2           777
   3           777

table_photos:

photo_id   photo_album_id
-------------------------
   1            1
   2            1
   3            1
   4            1
   5            2
   6            2
   7            3
   8            3
   9            3

Now I'd like to get the sum of all photos, created by a user, in my example album_creatoruser_id=777

I'm sure it would be possible directly in MySQL, with one query or subqueries, or with LEFT JOIN or GROUP BY, but I can't get it working because I don't understand the logic to build up this query.

Any help how to count the photo_id rows for all album_id's a specific album_creatoruser_id has created would be nice!

In short: How many photos album_creatoruser_id 777 got?

Upvotes: 0

Views: 101

Answers (2)

ryrysz
ryrysz

Reputation: 907

first possibility with subquery, count of all photo created by user 777:

 SELECT 
      COUNT(*)
 FROM photo
 WHERE
      photo_album_id IN 
      (
           SELECT album_id 
           FROM album 
           WHERE album_creatoruser_id = 777
      )

other:

 SELECT 
     COUNT(*)
 FROM photo p, album a
 WHERE 
     p.photo_album_id = a.album_id 
     AND a.album_creatoruser_id = 777

and with join

 SELECT 
      COUNT(*)
 FROM photo p 
 JOIN album a
 ON p.photo_album_id = a.album_id     
 WHERE 
      a.album_creatoruser_id = 777

Grouping here is not necessary, because We don't want present the data in groups.

Upvotes: 1

Arth
Arth

Reputation: 13110

Your basic query is:

   SELECT COUNT(*)
     FROM table_album ta
     JOIN table_photo tp
       ON tp.photo_album_id = ta.album_id     
    WHERE album_creatoruser_id = **input**

I suggest running the following queries in order to find out how it works:

   SELECT *
     FROM table_album ta
     JOIN table_photo tp
       ON tp.photo_album_id = ta.album_id

Would return all rows from table_album with their respective table_photo records under the condition tp.photo_album_id = ta.album_id.

   SELECT *
     FROM table_album ta
     JOIN table_photo tp
       ON tp.photo_album_id = ta.album_id
    WHERE album_creatoruser_id = **input**

Filters these rows to only those where album_creatoruser_id = **input**.

   SELECT COUNT(*)
     FROM table_album ta
     JOIN table_photo tp
       ON tp.photo_album_id = ta.album_id
    WHERE album_creatoruser_id = **input**

Counts up these rows, returning one aggregated row (COUNT() is an aggregate function like SUM() or MAX()) with the result, you don't need GROUP BY because there is only one group; all the filtered rows.

If you want all album_creatoruser_ids with their respective counts I would suggest:

   SELECT album_creatoruser_id, COUNT(*)
     FROM table_album ta
     JOIN table_photo tp
       ON tp.photo_album_id = ta.album_id
 GROUP BY album_creatoruser_id

Upvotes: 1

Related Questions