select COUNT with three table - mysql

Question

I am trying to select the COUNT with three tables with one single query (with WHERE conditions).

Here is my code which doesn't work correctly.

SELECT t1.count(id) AS car_model_count,t2.count(id) AS list_item_count,t3.count(id) 
    FROM `car_model` AS t1
    INNER JOIN `list_item` AS t2
    INNER JOIN `part_item` AS t3
    WHERE t1.user_id=3;

and what is the relations between these tables (parent <> foreign)? your join is incorrect - you are joining by what? better provide sample data from all tables and desired output. — mitkosoft, Apr 08 '16 at 11:34
Your `JOIN` is invalid and your logic is wrong. You can't count from multiple tables independantly using that syntax. Check http://stackoverflow.com/questions/3761240/count-from-multiple-tables-in-mysql — apokryfos, Apr 08 '16 at 11:42

score 1 · Accepted Answer · answered Apr 08 '16 at 11:45

Possible by using Sub-Query OR UNION is possible to get the COUNT from multiple table. Try this query :

SELECT 
    (SELECT count(*) FROM `car_model` WHERE user_id=3 ) AS car_model_count,
    (SELECT COUNT(*) FROM `list_item` WHERE user_id=3) AS list_item_count,
    (SELECT count(*) FROM `part_item` WHERE user_id=3) AS part_item_count;

select COUNT with three table - mysql

1 Answers1

Linked