CODEWITHSUNDEEP
mysql
santa
santa

Reputation: 12512

Count rows in second table with LEFT JOIN

I have a query where I output some results

SELECT
    t1.busName,
    t1.busCity,
    COUNT(t2.ofr_id) AS cntOffers
FROM t1
LEFT JOIN t2 ON (t2.ofr_busID = t1.busID)

The query above returns only one row, however, if I remove COUNT and leave only below query I get multiple results. What am I missing? And how can I fetch results from the first table while getting associated results count from t2?

SELECT
    t1.busName,
    t1.busCity
FROM t1
LEFT JOIN t2 ON (t2.ofr_busID = t1.busID)

Upvotes: 1

Views: 2235

Answers (2)

Alpha
Alpha

Reputation: 7858

It actually seems that the COUNT() in your first query is forcing a GROUP BY (because of the aggregation) on that field, which explains why you get only one row, but that does not imply that you only have one row in it.

Check out this SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE TestData (a int, b int);

INSERT INTO TestData
    (a, b)
VALUES
    (1, 1),
    (2, 2),
    (3, 3);

Query:

SELECT a, count(b) from TestData

Results:

| a | count(b) |
|---|----------|
| 1 |        3 |

As Gordon Linoff suggested, you need to use GROUP BY explicitly in order to replicate the same behavior without the COUNT.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You need group by:

SELECT t1.busName, t1.busCity,
       COUNT(t2.ofr_id) AS cntOffers
FROM t1 LEFT JOIN
     t2
     ON t2.ofr_busID = t1.busID
GROUP BY t1.busName, t1.busCity;

Most databases would return an error on your version of the query, because you have unaggregated and aggregated columns in the SELECT.

Upvotes: 5

Related Questions