MySQL Join and Count query

Question

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.

Yhn · Accepted Answer · 2012-06-15 14:19:16Z

3

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.

answered Jun 15, 2012 at 14:19

Yhn

2,8252 gold badges15 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

raina77ow Over a year ago

Sorry, but could you explain the point of grouping by two fields when the first one (id) supposed to be unique?

Yhn Over a year ago

As I stated in my answer, I don't think this is needed for MySQL. Other DBMS'es however, give an error when selecting fields that are not in the group aggregation. I don't directly have a MySQL instance running here at work; so I chose the most safe route :).

raina77ow Over a year ago

Well, I suppose I got too used to working with MySQL, where it's not necessary. ) +1 for your explanation.

raina77ow · Accepted Answer · 2012-06-15 14:17:18Z

1

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.

answered Jun 15, 2012 at 14:17

raina77ow

107k16 gold badges205 silver badges240 bronze badges

Comments

Henrique Ordine · Accepted Answer · 2012-06-15 14:30:02Z

1

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;

answered Jun 15, 2012 at 14:30

Henrique Ordine

3,3475 gold badges46 silver badges72 bronze badges

Comments

DonCallisto · Accepted Answer · 2012-06-15 14:26:47Z

0

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

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
Some DBMS requires that all group by fields, have to be present also into the SELECT ones

edited Jun 15, 2012 at 14:26

answered Jun 15, 2012 at 14:17

DonCallisto

30k9 gold badges74 silver badges104 bronze badges

2 Comments

MatBailie Over a year ago

If both tables have an id column, this gets nerfed. Also, I'm not sure why you added id to the SELECT when advert.id is already there.

DonCallisto Over a year ago

@Dems i dind't noticed that it was a previous id used in SELECT

Collectives™ on Stack Overflow

MySQL Join and Count query

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related