same column names with join, how to select the desired one-CodePudding

I have this query:

select bands_albums.name from (select * from bands join albums on bands.id = albums.band_id) as bands_albums;

but I get the following error:

Query 1: ERROR:  column reference "name" is ambiguous
LINE 3: select bands_albums.name from (select * from bands join albu...
        ^

as both bands and albums tables have the same column name, how to fix this error if I want the name of bands? I tried bands_albums.bands.name, but didn t work.

CodePudding user response：

Write always all columns that you really want and never only SELECT *

select 
   bands_albums.name 
from (select bands.name 
       from bands join albums on bands.id = albums.band_id) as bands_albums;

CodePudding user response：

Alias tables and subqueries should do the job...

select 
    ba.name as bandalbumname
from (
     select 
           b.name 
     from bands b 
     join albums a on b.id = a.band_id
     ) as ba;

Also dont use * on select subquerys as said before.

CodePudding user response：

You'll need to alias the tables. Also you shouldn't need to do a subquery in this case since you're just selecting a column from a join of all columns.

select b.name, a.name 
from bands b
join albums a on b.id = a.band_id;