nosthertus nosthertus - 5 months ago 7
MySQL Question

SQL: INNER JOIN SELECT makes the whole query return nothing

There are 3 tables, named as

account_has_account1, account_has_photos, photos_has_message_photos
where
account_has_account1
have columns
account_id, account_id1, status, type_id
and this table takes care on storing accounts following to another account

account_has_photos
stores information about all the photos one account has uploaded, it's columns are
photos_id, account_id, type_id
, this also stores
likes
according to
type_id


photos_has_message_photos
stores all messages posted to a photo, its a relational table from
photos
and
message_photos


i need to fetch a count of all likes from
account_has_photos
where
type_id = 1
which points to
like
from table
type


i have done this
SQL
:

SELECT account_has_photos.photos_id as id, "photos" as type, account_has_photos.update_at, account_has_photos.account_id, posts.total as total_messages, likes.total as total_likes
FROM account_has_account1
INNER 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(*) as total
FROM photos_has_message_photos
GROUP BY photos_has_message_photos.photos_id
) posts
ON(posts.photos_id = account_has_photos.photos_id)
INNER JOIN (
SELECT account_has_photos.photos_id, COUNT(account_has_photos.photos_id) as total
FROM account_has_photos
WHERE account_has_photos.type_id = 1
) likes
ON (likes.photos_id = account_has_photos.photos_id)
WHERE account_has_account1.account_id = 7 AND account_has_account1.`status` = "Active"


the first
INNER JOIN account_has_account1
is for showing all accounts that one account is following, The second
INNER JOIN photos_has_message_photos
is only for getting the count of all posted messages from a account's photos.

At this point everything goes fine, but when i insert the third
INNER JOIN account_has_photos
again, the query result is now 0, the purpose of this third
JOIN
is for getting a count of likes a photo has which is stored in
account_has_photos
where
type_id = 1


The rest is for setting the general conditions for the search.

Again the problem only happens in this query

INNER JOIN (
SELECT account_has_photos.photos_id, COUNT(account_has_photos.photos_id) as total
FROM account_has_photos
) likes
ON (likes.photos_id = account_has_photos.photos_id)


it could be that no likes are found on any photo, i have made the test for searching it separately and as i said, there is no like made on any photo, i didn't add any record because i want it to say 0 on count as it is going to be shown alot

Answer

here is much different way to write your query that should yeild the same results.

SELECT
    account_has_photos.photos_id as id
    ,"photos" as type
    ,account_has_photos.update_at
    ,account_has_photos.account_id
    ,COUNT(photos_has_messages.photos_id) as total_messages
    ,COUNT(DISTINCT likes.photos_id) as total_likes
FROM
    account_has_account1
    INNER JOIN account_has_photos
    ON account_has_photos.account_id = account_has_account1.account_id1
    AND account_has_photos.type_id = 17
    LEFT JOIN photos_has_message_photos
    ON photos_has_message_photos.photos_id = account_has_photos.photos_id
    LEFT JOIN account_has_photos likes
    ON likes.photo_id = account_has_photos.photo_id
    AND likes.type_id = 1
    WHERE account_has_account1.account_id = 7 AND account_has_account1.`status` = "Active"
GROUP BY
    account_has_photos.photos_id
    ,"photos"
    ,account_has_photos.update_at
    ,account_has_photos.account_id

I would recommend changing:

,COUNT(photos_has_messages.photos_id) as total_messages

to

,COUNT(DISTINCT photos_has_messages.WhateverTablesUniqueIdIs) as total_messages

Also this line

,COUNT(DISTINCT likes.photos_id) as total_likes

will always give you 1. so if likes does repeat photo_id then you also want to count whatever that account_has_photos unique identifier is....

Comments