nosthertus nosthertus - 5 months ago 7
MySQL Question

SQL: count rows from 3rd table

I have 3 tables, the first table is the

account_has_account1
where i store the relation between accounts and it's columns are
account_id, account_id1, status
where
account_id
is the account doing following to
account_id1
and
status
is an enum type with values
active, inactive
where
active
indicates if the account is actually following, if it's inactive, then account stopped following.

the second table is named
account_has_photos
which i store the photos one account has stored in the database, so it's columns are
account_id, photos_id
, so i need this table to get all photos from one account which another account is following.

But all these have messages posted on them, and here is where the 3rd table comes which is named
photos_has_message_photos
, from this table i only need a count of all posted messages in one photo, the columns are
photos_id, message_photos_id


for now my query is this:

SELECT account_has_photos.photos_id as id, "photos" as type, account_has_photos.update_at, account_has_photos.account_id
FROM account_has_account1
JOIN account_has_photos
ON (account_has_photos.account_id = account_has_account1.account_id1 AND account_has_photos.type_id = 17)
WHERE account_has_account1.account_id = 7 AND account_has_account1.`status` = "Active"


it shows all photos from accounts on which account id 7 is following, but on my attempts on getting the total messages have failed, i thought on doing an
INNER JOIN
like this:

INNER JOIN (
SELECT photos_has_message_photos.photos_id, count(photos_has_message_photos.photos_id) as total
FROM photos_has_message_photos
) posts
ON(posts.photos_id = account_has_photos.photos_id)


and then i select from main
posts.total
, but it does not show any row, not even the photos, the result is empty at this point and i have no idea why and what to do.

the complete query is like this:

SELECT account_has_photos.photos_id as id, "photos" as type, account_has_photos.update_at, account_has_photos.account_id, posts.total
FROM account_has_account1
JOIN account_has_photos
ON (account_has_photos.account_id = account_has_account1.account_id1 AND account_has_photos.type_id = 17)
INNER JOIN (
SELECT photos_has_message_photos.photos_id, count(photos_has_message_photos.photos_id) as total
FROM photos_has_message_photos
) posts
ON(posts.photos_id = account_has_photos.photos_id)
WHERE account_has_account1.account_id = 7 AND account_has_account1.`status` = "Active"


again, i only need a total of rows which are messages from each photos found

Answer

Try this query updated inner select

 SELECT ahp.photos_id as id, "photos" as type, ahp.update_at, ahp.account_id,posts.total
FROM account_has_account1 
    JOIN account_has_photos 
        ON (ahp.account_id = account_has_account1.account_id1 AND ahp.type_id = 17) INNER JOIN (
        SELECT phmp.photos_id, count(*) as total FROM photos_has_message_photos GROUP BY phmp.photos_id
    ) posts
        ON(posts.photos_id = ahp.photos_id) WHERE account_has_account1.account_id = 7 AND account_has_account1.`status` = "Active"