CODEWITHSUNDEEP
mysql
Robert
Robert

Reputation: 360

MySQL multiple joins and count distinct

I need to write query that joins several tables and I need distinct value from one table based on max count().

These are my tables names and columns:

bands:
db|name|

releases_artists:
release_db|band_db

releases_styles
release_db|style

Relations between tables are (needed for JOINs):

releases_artists.band_db = bands.db
releases_styles.release_db = releases_artists.release_db

And now the query that I need to write:

SELECT b.name, most_common_style
LEFT JOIN releases_artists ra ON ra.band_db = b.db

and here I need to find the most common style from all band releases

JOIN(
    SELECT DISTINCT style WHERE releases_styles.release_db = ra.release_db ORDER BY COUNT() DESC LIMIT 1
)

FROM bands b
WHERE b.name LIKE 'something'

This is just a non working example of what I want to accomplish. It would be great if someone could help me build this query.

Thanks in advance.

EDIT 1

Each artist from table bands can have multiple records from releases_artists table based on band_db and each release can have multiple styles from releases_styles based on release_db

So if I search for b.name LIKE '%ray%' it returns something similar to:

`bands`:
o7te|Ray Wilson
9i84|Ray Parkey Jr.

`releases_artists` for Ray Wilson:
tv5c|o7te (for example album `Change`)
78wz|o7te (`The Next Best Thing`)
nz7c|o7te (`Propaganda Man`)

`releases_styles`
tv5c|Pop
tv5c|Rock
tv5c|Alternative Pop/Rock
----
78wz|Rock
78wz|Pop
78wz|Classic Rock

I need style name that repeats mostly from all artist releases as this artist main style.

Upvotes: 2

Views: 265

Answers (1)

shmosel
shmosel

Reputation: 50716

Ok, this is a bit of a hack. But the only alternatives I could think of involve heaps of nested subqueries. So here goes:

SELECT name
     , SUBSTRING_INDEX(GROUP_CONCAT(style ORDER BY release_count DESC SEPARATOR '|'), '|', 1) AS most_common_style
  FROM (
        SELECT b.db
             , b.name
             , rs.style
             , COUNT(*) AS release_count
          FROM bands b
          JOIN releases_artists ra ON ra.band_db = b.db
          JOIN releases_styles rs ON rs.release_db = ra.release_db
         GROUP BY b.db, rs.style
       ) s
 GROUP BY db;

Upvotes: 1

Related Questions