sql - How to use field from subquery in MySQL?

Question

I've got the following query:

SELECT insent.id, notifications.id
FROM insent
WHERE insent.id IN (
    SELECT insent_id
    FROM notifications
)
;

But this gives an error saying:

Unknown column 'notifications.id' in 'field list'

Any idea how I can do this?

Answer 1

If you want to get data from few tables in one query then you should use JOIN construction. For example:

SELECT 
    insent.id, 
    notifications.id 
FROM insent 
JOIN notifications ON notifications.insent_id = insent.id

if you don't want it you must remove notifications.id field from field list

SELECT 
   insent.id
FROM insent
WHERE insent.id IN (
    SELECT insent_id FROM notifications
)