Displaying Foreign Key Referenced value Postgresql

Question

I have 3 tables: musiccollection: id (pk), title, artist.. etc etc musiccollection_playlist: mcpid (pk), playlistid (fk:playlist.playlistid), trackid(fk:musiccollection.id) playlist: playlistid (pk), playlistname

I want to display the whole rows in musiccollection as shown in the musiccollection_playlist table respective foreign key.

ex. in musiccollection table

id    title    artist 
1     song1    artist1
2     song2    artist2
3     song3    artist3
4     song4    artist4
5     song5    artist5

ex. in playlist table

playlistid    playlistname
1             playlist1
2             playlist2

ex. in musiccollection_playlist

mcpid    playlistid    trackid
1        1             2
2        1             4
3        1             5

Now I want to display the data in musiccollection_playlist WHERE playlistid is 1 (general example). However I want the returned data to be the actual rows in music_collection table where the id's chosen would be 2,4,5.

I am attempting to write the query:

SELECT * FROM musiccollection as a, musiccollection_playlist as b WHERE a.id IN (SELECT trackid FROM musiccollection_playlist) AND b.playlistid = '1'

I have to be reasoning something out wrongly or I have something wrong in the query. How can I fix this to achieve what I want? i.e. display the whole row identified by the trackid in musiccollection_playlist.

In reality I can do something like:

SELECT trackid FROM musiccollection_playlist WHERE playlistid = '1'

The trackid then has to reference from the musiccollection table.

What do you suggest?

Politank-Z · Accepted Answer · 2015-04-10 18:12:02Z

1

Your condition:

WHERE a.id IN (SELECT trackid FROM musiccollection_playlist)

will only limit the results to every track which is in any playlist.

I think you need to review the documentation on joins. They can make life a lot simpler:

SELECT a.* FROM musiccollection a INNER JOIN musiccollection_playlist b ON
  a.id = b.trackid
WHERE b.playlistid = '1';

answered Apr 10, 2015 at 18:12

Politank-Z

3,7433 gold badges27 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Displaying Foreign Key Referenced value Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related