CODEWITHSUNDEEP
mysql
Adrian Tanase
Adrian Tanase

Reputation: 700

very complex SELECT

I want to do a complex SELECT between more tables (4+) that will order and count items.

So far this is what my line is : 

SELECT
  myl_u.id, 
  myl_u.label_real_address, 
  myl_u.ext, 
  COUNT(myc_c.contact_id), 
  COUNT(myl_r_c.release_id) 
FROM
  myl_users myl_u  
  LEFT JOIN myc_contacts myc_c  ON myc_c.contact_type='l' AND myc_c.contact_id=myl_u.id 
  LEFT JOIN myl_releases myl_r ON myl_r.id=myl_u.id 
  LEFT JOIN myl_r_comments myl_r_c ON myl_r.release_id=myl_r_c.release_id 
GROUP BY myl_u.label_real_address 
ORDER BY COUNT(myc_c.contact_id) DESC

It's half working, but when I add the latter part of the SQL, it shows unexpected values and it doubles them too somehow.

Basically I have myl_users (a collection of record labels) myc_contacts (how many favourites does a user have, contact_type='l' means it's about myl_users and not other table) myl_releases contains music releases (like EP, album, with unique id's and myl_r_comments contains comments regular users do to these releases.

I managed to ORDER by how many favourites a record label has (15, 14, 10, 8..) - the COUNT(myc_c.contact_id) clause

but when I add the next clause and make the query bigger to order by the total comments the releases from labels have, unexpected appears.

Can someone pinpoint me to the right way ?

I will comment and adapt / clarify the question by your needs.

thanks, have a happy new year

Upvotes: 1

Views: 95

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The problem is that you are summing along multiple dimensions, so you are getting a cross product. The best way is to summarize along each dimension independently:

SELECT myl_u.id, myl_u.label_real_address, myl_u.ext, 
       sum(myc_c.cnt), 
       sum(myl_rc.cnt) 
FROM myl_users myl_u LEFT JOIN
     (select contact_id, count(*) as cnt
      from myc_contacts myc_c
      where myc_c.contact_type='l'
      group by contact_id
     ) myc_c
     ON myc_c.contact_id=myl_u.id LEFT JOIN
     (select myl_r.id, count(*) as cnt
      from myl_releases myl_r LEFT JOIN
           myl_r_comments myl_r_c
           ON myl_r.release_id=myl_r_c.release_id
      gropu by myl_r.id
     ) myl_rc
     ON myl_rc.id=myl_u.id 
GROUP BY myl_u.id, myl_u.label_real_address, myl_u.ext
ORDER BY 4 DESC

It is not clear from the question whether the final group by is necessary. If there are no duplicates in the myl_u table, then you don't need the outside aggregation at all.

Upvotes: 1

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103145

At least one problem that I spot is that you need a WHERE clause if you want to restrict the rows. The JOINs should only include the considtions of the JOINs.

SELECT
  myl_u.id, 
  myl_u.label_real_address, 
  myl_u.ext, 
  COUNT(myc_c.contact_id), 
  COUNT(myl_r_c.release_id) 
FROM
  myl_users myl_u  
  LEFT JOIN myc_contacts myc_c  ON myc_c.contact_id=myl_u.id 
  LEFT JOIN myl_releases myl_r ON myl_r.id=myl_u.id 
  LEFT JOIN myl_r_comments myl_r_c ON myl_r.release_id=myl_r_c.release_id 
WHERE
   myc_c.contact_types = '1'
GROUP BY myl_u.label_real_address 
ORDER BY COUNT(myc_c.contact_id) DESC

Also, are you sure its a left join that you want? That returns all rows from the "left" table even if no matching values on the right. Try changing LEFT to INNER and see if you get what you are expecting.

Upvotes: 0

Related Questions