CODEWITHSUNDEEP
mysqlsqldatabase
gbuckingham89
gbuckingham89

Reputation: 385

MySQL Join and Count query

I have two MySQL tables structed as follows;

advert

+-------------------+--------------+
| Field             | Type         |
+-------------------+--------------+
| id                | int(11)      |
| title             | varchar(150) |
+-------------------+--------------+

advert_interest_record

+--------------+------------+
| Field        | Type       |
+--------------+------------+
| id           | int(11)    | 
| advert_id    | int(11)    |
| message      | longtext   |
+--------------+------------+

I want to select a list of all records from the "advert" table - and get the number of rows from the "advert_interest_record" table where advert.id=response.advert_id.

The SQL I'm using to join the tables is as follows;

SELECT advert.id, advert.title 
FROM advert 
LEFT JOIN advert_interest_record ON advert.id = advert_interest_record.advert_id  
GROUP BY advert_interest_record.advert_id

The first problem I've got is that when there are no records in the "advert_interest_record" table, the record from the "advert" table isn't included in the result - and I need it to be.

My second question is, how would I go about counting the rows in the "advert_interest_record" table?

Any advice on this would be appreciated.

Thanks.

Upvotes: 0

Views: 135

Answers (4)

Henrique Ordine
Henrique Ordine

Reputation: 3337

Group By advert.id instead, and use count() to count your records.

The adverts that have no advert_interest_record should return a count() equals zero.

SELECT advert.id, count(advert_interest_record.id) as interestcount
  FROM advert 
  LEFT JOIN advert_interest_record ON advert.id = advert_interest_record.advert_id  
GROUP BY advert.id;

Upvotes: 1

DonCallisto
DonCallisto

Reputation: 29912

SELECT advert.id, advert.title, count(*) as totalRows 
FROM advert 
LEFT JOIN 
         advert_interest_record ON advert.id = advert_interest_record.advert_id  
GROUP BY advert.id, advert.title

Some notes about the query

  1. GROUP BY advert_interest_record.advert_id that is from your original query. You group by a field that isn't into the SELECT ones. This cause your issue
  2. Some DBMS requires that all group by fields, have to be present also into the SELECT ones

Upvotes: 0

Yhn
Yhn

Reputation: 2815

First of all; you're selecting two fields from the advert table; while gouping on one field from the advert_interest_record table.

Assuming that for all advert.id values, the advert.title value is the same, it might give correct results.

Anyways; since you want the data in advert to be leading, it's best to group on the fields of that table that you want uniquely back (which also helps in getting the count of records associated with that in the other table).

For example, you could query as following:

SELECT a.id, a.title, COUNT(air.id)
FROM advert a
LEFT JOIN advert_interest_records air ON a.id = air.advert_id
GROUP BY a.id, a.title

This would give you a result of all unique combinations of advert.id and advert.title, together with the number of records in advert_interest_records that are associated with the advert.id value.

I am assuming that advert.id is unique, and that every id only occurs once. Otherwise the grouping on both id and title (which is not per se needed for MySQL, I believe, in contrast to other DBMS'es), might give you incorrect results.

Upvotes: 3

raina77ow
raina77ow

Reputation: 106375

How about this:

   SELECT a.title, COUNT(air.id) 
     FROM advert a 
LEFT JOIN advert_interest_record air
       ON a.id = air.advert_id
    GROUP BY a.id

LEFT JOIN should guarantee that all the records from the advert table will be included in the final result.

Upvotes: 1

Related Questions