Vince Vince - 11 months ago 60
JSON Question

LEFT JOIN with id columns for all joined tables

I have two tables that I am applying a join to. Table A has a foreign key that references rows from Table B. SQL is as follows:

FROM TableA AS a
LEFT JOIN TableB AS b ON = b.tableAId
WHERE a.ownerId = X

I am getting the desired result except for one thing. That is when returning the rows in JSON, only one id column is shown (TableB).

Instead I want to be able to return all id columns in the JSON where duplicate columns would have a number appended to it. For example: id, id1, id2, id3 etc...

Answer Source

You need to specify the columns that you want, explicitly giving them aliases so the names are different. Something like this:

SELECT a.*, as b_id
     TableB b 
     ON = b.tableAId
WHERE a.ownerId = X;