CODEWITHSUNDEEP
mysqlsqlcount
Scott
Scott

Reputation: 6251

MySQL query and count from other table

I would like to get the data from one table, and count all results from other table, depending on the first table data, here is what I tried:

SELECT
    cars.*, (
        SELECT
            COUNT(*)
        FROM
            uploads
        WHERE
            uploads.cid = cars.customer
    ) AS `count`,
FROM
    `cars`
WHERE
    customer = 11;

I dont really have an idea why its not working, as I'm not a regular MySQL user/coder...

Could anyone direct me in the right direction with this one?

Upvotes: 0

Views: 117

Answers (4)

Sashi Kant
Sashi Kant

Reputation: 13465

Try this :

SELECT  customer, COUNT(cid) totalCount
FROM    cars 
        INNER JOIN uploads 
            ON (customer = cid)
WHERE   customer = 11
GROUP BY customer

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

SELECT  cars.*,COUNT(uploads.*) as uplloaded 
from cars 
left outer join uploads on  uploads.cid = cars.customer
where  cars.customer = 11 
group by uploads.cid;

Upvotes: 0

Mihai Matei
Mihai Matei

Reputation: 24276

SELECT
    c.*, COUNT(u.cid) AS count
FROM
    cars c
LEFT JOIN 
    uploads u
ON
    u.cid=c.customer
WHERE
    u.customer = 11;
GROUP BY c.cid

Upvotes: 1

John Woo
John Woo

Reputation: 263693

Try it by joining both tables using LEFT JOIN

SELECT  a.customer, COUNT(b.cid) totalCount
FROM    cars a
        LEFT JOIN uploads b
            ON a.customer = b.cid
WHERE   a.customer = 11
GROUP BY a.customer

using COUNT(*) in LEFT JOIN will have records to have a minimum count of 1.

Upvotes: 1

Related Questions