CODEWITHSUNDEEP
phpmysqlcount
aayushi
aayushi

Reputation: 161

How to get count from another table (zero when no data) using single query in mysql?

I have two tables - 'Offered' and 'joined'. I need count of 'offered_id' from joined table and zero when not found. My tables and output required is below. How can I achieve this using single query in mysql ? 

TABLE : OFFERED
===============
offered_id    data
-----------   ----
    1         aaaa
    2         bbbb
    3         cccc
    4         dddd
    5         eeee
    6         ffff

TABLE : JOINED
===============
joined_id      offered_id
-----------    ----------
    1               5
    2               2
    3               2
    4               1
    5               3
    6               2
    7               5

OUTPUT REQUIRED
===============
offered_id      data     count(offered_id) from joined table.(0 for no entry)
-----------    -----     ------------------------------------------------
    1           aaaa          1
    2           bbbb          3
    3           cccc          1
    4           dddd          0
    5           eeee          1
    6           ffff          0

Upvotes: 1

Views: 87

Answers (3)

Sarvagna Mehta
Sarvagna Mehta

Reputation: 340

Give it a try it helps me to achieve the same thing for my project and it will surely help you.

SELECT offered_id FROM OFFERED WHERE offered_id IN (SELECT DISTINCT offered_id FROM JOINED)

Upvotes: 1

Manashvi Birla
Manashvi Birla

Reputation: 2843

Use count :

SELECT OFFERED.OFFERED_id,OFFERED.data,(select count(JOINED.joined_id) FROM JOINED INNER JOIN OFFERED
ON JOINED.offered_id = OFFERED.offered_id) as count_joined FROM OFFERED

Upvotes: 1

NULL
NULL

Reputation: 1858

Using sub query you can achieve that...

SELECT OFFERED.offered_id, OFFERED.data, (SELECT COUNT(JOINED.joined_id) FROM JOINED WHERE JOINED.offered_id = OFFERED.offered_id) AS count_joined FROM OFFERED

Upvotes: 0

Related Questions