mysql - select records with one query -

mysql - select records with one query -

these tables construction :

data :

id | name -----+------- 1 | d1 2 | d2 3 | d3

supplier :

id | data_id | name -----+-------------+---------- 1 | 1 | microsot 2 | 1 | google 3 | 1 | yahoo 4 | 2 | apple

connector :

id | supplier_id | name -----+----------------+----------- 1 | 2 | test 1 2 | 1 | test 2 3 | 4 | test 3

now want select columns of data records , count() of records in connector table data_id in supplier table equal id of data table.

the result should this:

id | name | count() ------+-----------+--------- 1 | d1 | 2 2 | d2 | 1 3 | d3 | 0

how can 1 query in mysql ?

select data.id, data.name, count(distinct connector.id) info left bring together supplier on (data.id = supplier.data_id) left bring together connector on (supplier.id = connector.supplier_id) grouping data.id, data.name

mysql sql join count