CODEWITHSUNDEEP
mysqlsqlsubquery
Dexter Naru
Dexter Naru

Reputation: 233

coverting join to subquery on mysql

I need to change the following code to use subqueries:

SELECT artist.name AS Banda, album.title AS Album, track.name AS Canción 
FROM artist
INNER JOIN album
ON artist.artistid = album.artistid
INNER JOIN track
ON album.albumid = track.albumid;

What I've tried until now is this:

SELECT artist.name AS Banda, album.title AS Album, track.name AS Canción
FROM artist, album,track
WHERE artist.artistid IN (SELECT album.artistid FROM album, track where album.albumid = track.albumid);

Upvotes: 2

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Not using JOIN in MySQL is rather complicated:

select (select a.name from artist where a.artistid = t.artistid) as banda,
       t.albumname,
       t.Canción
from (select t.*,
             (select al.title from album al where al.albumid = t.albumid) as albumname,
             (select al.artistid from album al where al.albumid = t.albumid) as artistid
      from track t
     ) t;

The query that you want to write:

select (select a.name
        from artist a
        where a.artistid in (select al.artistid
                             from album al
                             where al.albumid = t.albumid
                            )
       ) as banda,
       (select al.title
        from album al
        where al.trackid = t.trackid
       ) as album,
       t.name as Canción
from tract t;

Unfortunately, MySQL does not usually recognize nested correlation clause references.

Upvotes: 3

Related Questions