Alex Ward Alex Ward - 1 year ago 58
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:




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

I have the following query string:

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

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: ''

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

If so can someone please tell me how?

Answer Source

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.*, as publisher_name, as type_name
FROM comics c JOIN
     publishers p
     ON c.publisher = JOIN
     comic_types ct
     ON c.type =;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download