CODEWITHSUNDEEP
sqlpostgresqlselect
Dennis
Dennis

Reputation: 97

How to resolve an id by an foreign table

I want to resolve an ID by another table, where the name of this id is stored. 

SELECT d.id_data, string_agg(s.name_last,', ') AS authors, d.title, i.name
FROM data d, institution i, staffs s
WHERE d.id_staffs = s.id_staffs 
    AND d.id_institution = i.id_institution 
GROUP BY d.id_data limit 100 ;

But how can I get the name of my Institution. I want that the SELECT shows me the institution name, which has stored the data. Something like that

id_data | authors               | title    | name
----------------------------------------------------------------
1       |Mustermann, Musterfrau | sunmaker | university cologne
2       |Schmidt, Müller        | dry age  | university berlin

I just need to resolve the id of the institution to his name.

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Always use proper, explicit, standard JOIN syntax. Never use commas in the FROM clause.

Presumably, you want something like this:

SELECT d.id_data, string_agg(s.name_last,', ') AS authors,
       d.title, i.name
FROM data d JOIN
     institution i
     ON d.id_staffs = s.id_staffs JOIN
     staffs s
     ON d.id_institution = i.id_institution 
GROUP BY d.id_data, d.title, i.name 
LIMIT 100 ;

That is, fix the GROUP BY to have all the unaggregated columns.

Upvotes: 1

Related Questions