CODEWITHSUNDEEP
node.jspostgresql
Alex Foxleigh
Alex Foxleigh

Reputation: 1974

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?

Upvotes: 0

Views: 39

Answers (2)

Robert Columbia
Robert Columbia

Reputation: 6418

It looks like you may be having collisions between identical column names. Explicitly specify the columns you are interested in.

'SELECT comics.id, comics.issue, comics.series, publishers.name as publisher, type.name as type FROM comics JOIN publishers ON (publisher = publishers.id) JOIN comic_types ON (type = comic_types.id);'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 2

Related Questions