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
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments