CODEWITHSUNDEEP
phpmysql
Utku Dalmaz
Utku Dalmaz

Reputation: 10172

mysql query that can be used instead mysql_num_rows

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 ?

Upvotes: 0

Views: 123

Answers (3)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

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

Upvotes: 1

Nico Huysamen
Nico Huysamen

Reputation: 10417

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

Upvotes: 1

JonVD
JonVD

Reputation: 4268

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

Upvotes: 3

Related Questions