Mysql – How to count the number of different child id’s belonging to a parent record

MySQL

For instance I have two tables in a music database.

Table album and table track.

Each track record has (amongst other things), an album_id and an artist_id

Each album record has a unique album_id

In order to spot which albums are by 'various artists' I want to count the number of distinct artist_id's which belong to each album.

Such that two or more track records with the same album_id but with differing artist_id's should signal that I can flag the album as 'various artists'

I can do it with multiple queries but I'm seeking the elegant all in one query.

Best Answer

There are various ways to solve this problem, but with a composite index on (album_id, artist_id) most DBMS will manage to use only this index (a full index scan) with the following query and thus be quite efficient:

SELECT album_id
FROM track
GROUP BY album_id
HAVING MIN(artist_id) < MAX(artist_id) ;

If you also want the number of artists per album, you can add COUNT(DISTINCT artist_id) in the select list.

A less obfuscated alternative condition is to use (which could be less efficient than the above MIN < MAX method):

HAVING COUNT(DISTINCT artist_id) > 1 ;

Related Solutions

Mysql – Relation between multiple hierarchical taxonomies linked to a single entity

Taxonomy -> TaxonomyTerm
TaxonomyTerm -> Subject
Subject -> Topic
Topic -> Question

Each of these lines is a 1 to many relationship, e.g. A single Taxonomy can have 1 or more TaxonomyTerms, and these terms can be related to multiple Taxonomies. I think you mostly have it but I'm not seeing a need to link one TaxonomyTerm directly with another.

Mysql – Pivot table with MySQL

Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;


SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');



PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)

Best Answer

Related Solutions

Mysql – Relation between multiple hierarchical taxonomies linked to a single entity

Mysql – Pivot table with MySQL

Related Question