Vince Vince - 1 month ago 7
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:

SELECT *
FROM TableA AS a
LEFT JOIN TableB AS b ON a.id = 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

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

SELECT a.*, b.id as b_id
FROM TableA a LEFT JOIN
     TableB b 
     ON a.id = b.tableAId
WHERE a.ownerId = X;
Comments