CODEWITHSUNDEEP
mysqlsql
BernardA
BernardA

Reputation: 1523

mysql count(*) in joins with one-to-many table

I need to perform a COUNT on a quite a big query, where one of the joined tables has a one-to-many relationship. This is throwing off my result as all data is being multiplied by the number of times an item is repeated in the 'many' side of the one-to-many table.

This is a shortened version of the query showing only the relevant portion to highlight the issue:

SELECT COUNT(trimtype) FROM versiontrim 
INNER JOIN trims USING (trim_id) 
INNER JOIN prices USING(version_id) 
INNER JOIN m_versions USING(version_id)
WHERE trimtype IN('sec', 'help') AND price BETWEEN 200001 AND 210000 
GROUP BY version_id

All tables are quite straighforward except m_versions that has the one-to-many relationship and looks like this:

version_id       serv_id

  1                 1
  1                 2
  1                 3
  1                 4
  1                 5
  .... and so on

The expected result of the query is :

version_id  COUNT(trimtype)     
44          9
54          7
69          9
214         10
216         6
282         1
290         10

Instead I am getting this,ie, all counts multiplied by 5 which is the number of times version_id is repeated in the m_versions table:

version_id  COUNT(trimtype)     
44         45
54          35
69          45
214         50
216         30
282         5
290         50

How to avoid this behavior? Thanks

Upvotes: 2

Views: 762

Answers (1)

John Woo
John Woo

Reputation: 263703

It matches to multiple records on table m_version that is why you are getting invalid result. Try wrapping it a subquery,

INNER JOIN (SELECT DISTINCT version_id FROM m_versions) m USING(version_id)

UPDATE

So the full query will look like this,

SELECT version_id, COUNT(trimtype) 
FROM   versiontrim 
       INNER JOIN trims USING (trim_id) 
       INNER JOIN prices USING(version_id) 
       INNER JOIN (SELECT DISTINCT version_id FROM m_versions) m USING(version_id)
WHERE  trimtype IN('sec', 'help') AND price BETWEEN 200001 AND 210000 
GROUP  BY version_id

Upvotes: 2

Related Questions