Alex Ward Alex Ward - 3 months ago 7
Node.js Question

populating data from a join

Although I know a bit of SQL, I have never really used relational databases before, as I realise this is a powerful thing I'm trying to learn how to do it in PostgreSQL. I currently have a three database tables I'm trying to connect, they are as follows:

comics:
id(int)
issue(int)
series(varchar[255])
publisher(int)
type(int)

publishers:
id(int)
name(varchar[255])
url(varchar[255])

type:
id(int)
name(varchar[255])


In each table the ID is the primary key and there is an fkey constrain on comics.publisher (to publishers.id) and comics.type (to type.id)

I have the following query string:

'SELECT * FROM comics JOIN publishers ON (publisher = publishers.id) JOIN comic_types ON (type = comic_types.id);'


Which sort of works but it doesn't do what I expected, what I was hoping was to get back the following result:

id: 1,
issue: 1,
series: 'Civil War',
publisher: 'Marvel',
type: 'single issue'


However what I got was:

id: 1,
issue, 1,
series: 'Civil War',
publisher: 1,
type: 1,
name: 'Single issue',
url: 'marvel.com'


If I have to namespace my entries then so be it but surely there is a way to pull the publishers.name into comics.publisher and type.name into comics.type?

If so can someone please tell me how?

Answer

I strongly recommend that you use table aliases and qualify all column names. You should list the columns that you want in the SELECT to be sure there are no naming collisions. Something like this:

SELECT c.*, p.name as publisher_name, ct.name as type_name
FROM comics c JOIN
     publishers p
     ON c.publisher = p.id JOIN
     comic_types ct
     ON c.type = ct.id;
Comments