SQL: get attributes from nested query

Question

How can I return d.title or u.name in the outer SELECT clause?

SELECT c.id, c.name
FROM components c 
INNER JOIN publications p 
ON c.id = p.component_id
AND p.document_id IN 
    (SELECT d.id FROM documents d WHERE user_id IN 
        (SELECT u.id FROM users u WHERE u.brand_id IN (39, 41)
    )
)

I get this error when I throw d.title in the top line:

missing FROM-clause entry for table "d" LINE 1

The package I'm using needs these values returned on the top line to make any use out of them in the result.

Structure

A User has many Documents, and Publications is the join table between Documents and Components.

It would be easier to give a definitive answer if you showed the structure of the tables, but in general, using joins instead of IN clauses will let you refer to the tables to which you are applying criteria. — rd_nielsen
– rd_nielsen, Commented Jul 6, 2017 at 18:47
As @rd_nielsen said, you will need to join in these tables (p.document_id = documents.id and documents.user_id = users.id) if you want to reference them in the select statement. — Jacob H
– Jacob H, Commented Jul 6, 2017 at 18:50

Ankit Bajpai · Accepted Answer · 2017-07-06 19:01:54Z

2

Use below query -

SELECT c.id, c.name, d.title, u.name
FROM components c 
INNER JOIN publications p ON c.id = p.component_id
INNER JOIN documents d ON d.id = p.document_id
INNER JOIN users u ON d.user_id = u.id
AND u.brand_id IN (39, 41)

Hope this helps.

answered Jul 6, 2017 at 19:01

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

t56k Over a year ago

That helps so much. Cheers!

Collectives™ on Stack Overflow

SQL: get attributes from nested query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related