I want to know how i can write a mysql query which checks how many record exists in other table ?
table 1 : names
id name
-- ----
4 john
5 mike
6 jenny
table 2 : cars
id name_id car
-- ------- ---
1 4 bmw
2 4 wv
3 6 honda
query = "SELECT * FROM names WHERE id = '4'" <- in that query i also want to check how many records there are in the cars table or if there is any ?
This should do it:
SELECT names.id, name, COUNT(car) as 'cars'
FROM names LEFT JOIN cars ON names.id = cars.nameid
WHERE names.id = '4'
GROUP BY names.id, name
SELECT n.*, COUNT(c.*) FROM names n, cars c WHERE n.id = 4
You can also name the column:
SELECT n.*, COUNT(c.*) AS `num_cars` FROM names n, cars c WHERE n.id = 4
Add the amount of cars for each person in an additional LEFT OUTER JOIN
SELECT n.*, c.cnt
FROM names n
LEFT OUTER JOIN (
SELECT name_id, cnt = COUNT(*)
FROM cars
GROUP BY
name_id
) c ON c.name_id = n.id
WHERE ID = 4
If performance is an issue, you can add the condition to the left join at the expense of maintainability.
SELECT n.*, c.cnt
FROM names n
LEFT OUTER JOIN (
SELECT name_id, cnt = COUNT(*)
FROM cars
WHERE name_id = 4 -- Condition needs to be updated in two places
GROUP BY
name_id
) c ON c.name_id = n.id
WHERE ID = 4
