CODEWITHSUNDEEP
sqloracle-database
Sebastian Ampuero Morisaki
Sebastian Ampuero Morisaki

Reputation: 337

how to group values from different columns sql

Ok ive got 2 important tables which are TRACK and ALBUM. enter image description here

(Playlisttrack is not important). What I want to do is list all the composers who edited a whole album from another artist, that is, the composer who edited all tracks from one same album.

For example: enter image description here

In this sample, composer "a" edited all songs x1-x5 from album1. Songs y1-y5 have composer "d" and also "t", so that is not what I want to display. From my sample I want to get something like this

enter image description here

Explaning it a little better: I want to show all composers who have edited all the songs of one album.That is, I have to get all the songs from one album and compare if they all have the same composer, I dont know how to do that.

So far I reached this point where all artists, albums, tracks and composers are displayed.. I didnt come any further

select tr.COMPOSER,tr.NAME,alb.title,art.name as Artist
from TRACK tr 
join album alb 
on tr.ALBUMID = alb.albumid
join artist art 
on artistid = alb.artistid
order by alb.title ASC;

Upvotes: 3

Views: 70

Answers (2)

SQLChao
SQLChao

Reputation: 7837

I think this does what you want. I used a derived table to figure out which albumids only have one composer and joined this back to your query.

select tr.COMPOSER,tr.NAME,alb.title,art.name as Artist
from TRACK tr 
JOIN (SELECT AlbumID, COUNT(1) as Num_Tracks FROM Track GROUP BY  AlbumID HAVING COUNT(DISTINCT Composer) = 1) tr2
ON tr.albumid = tr2.albumid
join album alb 
on tr.ALBUMID = alb.albumid
join artist art 
on artistid = alb.artistid
WHERE num_tracks > 1
order by alb.title ASC;

Upvotes: 1

krokodilko
krokodilko

Reputation: 36087

I want to show all composers who have edited all the songs of one album.That is, I have to get all the songs from one album and compare if they all have the same composer,

If the composer edited all tracks of the whole album, it means that there was no any other composer that edited this album.
Just check, for given album and composer, whether another composer exists for this album or not. The latter condition means, that there was only one composer who edited that album.

SELECT DISTINCT t1.albumid, t1.composer
FROM track t1
WHERE NOT EXISTS (
   SELECT 1
   FROM track t2
   WHERE t1.albumid = t2.albumid
     AND t1.composer <> t2.composer
)

Upvotes: 1

Related Questions