How to populate query with result from different table?

Question

I have two tables. Table A and table B. Table A has a column that is a reference to the primary key to table B. I want to run a select query on table A and then populate the column that referrers to B with all of the data in that row of B.

SELECT * from A a LEFT JOIN B b ON a."b_id" = b."id"  WHERE ...

That gives a result with each row containing all of the columns of A and all of the columns of B. It is a confusing mess to figure out which column is from which table. I want to be able to do something like.

row.A."column name"
row.B."column name"

I don't want to have to rename every single column using AS. There must be a better way to do this.

Well, you need to rename the columns if you want them to have different names. — Gordon Linoff, Jun 05 '17 at 22:05
Possible duplicate: https://stackoverflow.com/questions/39805736/get-join-table-as-array-of-results-with-postgresql-nodejs — vitaly-t, Jun 05 '17 at 22:20

score 0 · Answer 1 · answered Jun 05 '17 at 23:20

Not a 100% sure what your asking but what I think your asking is.
You want a way to have only column B values to show? If so you could do:

SELECT B.*
FROM A
JOIN B
  ON A.b_id = B.id

That will only get you the B columns and data, If you want A also maybe do but you want to have it separate from b maybe do:

SELECT B.*,'|' AS ['|'], A.*
FROM A
JOIN B
  ON A.b_id = B.id

Hopefully this is helpful, if not to you maybe another reader.

How to populate query with result from different table?

1 Answers1