Can I do a SQL select on a subquery and also retrieve the subquery?-CodePudding

I have the following SQL:

SELECT id, user_id, coordinates 
FROM fields 
WHERE id IN (SELECT field_id FROM transactions WHERE id IN (11,10,12))

There are 2 tables: transactions and fields. Both of them have their own id field. However, in transactions there's also a field to connect each row to the fields.id called field_id. I have a list of transactions.id, and I would like to obtain a few fields from table fields, but I would like to obtain too the transactions.id associated. For example for the first row it would be:

fields.id (for transactions.id=11), fields.user_id (for transactions.id=11), fields.coordinates (for transactions.id=11), 11

and so on.

Is this possible? I will do these queries using python with postgresql v14.

CodePudding user response：

If I understood the question correctly

SELECT * FROM fields f
join transactions t on f.id = t.field_id
WHERE t.id in (11,10,12)

CodePudding user response：

SELECT t.id as lower_id, f.id as higher_id FROM fields f
join transactions t on f.id = t.field_id
WHERE t.id in (11,10,12)