CODEWITHSUNDEEP
mysqlsubquery
gus
gus

Reputation: 785

MySql subquery value doesnt show the correct result

How do I rewrite this query to show the correct value for the sub query claims if there isn't any record in listings for this account_id, but there is 1 record in claimed_listings. Thanks!

SELECT status, (SELECT count(id) 
               FROM claimed_listings 
               WHERE account_id = 1) AS claims  
FROM listings 
WHERE account_id = 1

I'm expecting to see a result like

status | claims
     A | 1
     F | 1
     E | 1

in this case there are three listings and 1 claim listing. Problem is if there is no listing(s) and 1 claim listing i get no results at all?

Upvotes: 0

Views: 112

Answers (2)

John Woo
John Woo

Reputation: 263893

try this:

SELECT a.Account_ID, Count(b.Account_ID) TotalAcount
FROM claimed_Listings a LEFT JOIN listings b
        on a.account_ID = b.Account_ID
WHERE a.Account_ID = 1
GROUP BY a.Account_ID

Upvotes: 2

Korhan Ozturk
Korhan Ozturk

Reputation: 11320

Try the following (hope I got you right..)

select count(cl.id), count(cl.account_id)
from claimed_listings cl
where cl.account_id=1 and cl.account_id not in (select account_id from listings)
group by cl.id
having count(cl.account_id) > 0;

Upvotes: 0

Related Questions