MySQL SubQuery with query variables

Question

I'm trying to do the following MySql query and when i go to run it, it tells me that "q.object_id" in the sub-query is unknown (if i change it to o.id, it says the same).

SELECT q.*, (SELECT c.title 
             FROM api_course c 
             LEFT OUTER JOIN api_object_parents op 
                 ON c.object_id = op.parent 
                     AND op.object_id = q.object_id) as parent_title
    FROM api_quiz q
    LEFT OUTER JOIN api_object o ON q.object_id = o.id
    WHERE o.type = 'Quiz'

Basically i need to get the same id that is being used in the main query and use that in a sub-query.

Thanks!

try use "q"."*", "o"."objectidwhatever" mysql is boring

Guerra
– Guerra

2012-08-21 04:06:50 +00:00
Commented Aug 21, 2012 at 4:06 — Guerra
– Guerra, Commented Aug 21, 2012 at 4:06

John Woo · Accepted Answer · 2012-08-21 04:12:08Z

4

is this what you are looking for?

SELECT  a.*, d.title
FROM    api_quiz a
            LEFT JOIN   api_object b
                on a.object_id = b.id
            LEFT JOIN   api_object_parents c
                ON c.parent = a.object_id
            LEFT JOIN   api_course d 
                ON c.object_id = c.parent

answered Aug 21, 2012 at 4:12

John Woo

265k70 gold badges508 silver badges500 bronze badges

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

1 Comment

Michael Lynch Over a year ago

Haha, yea i was just about to answer my own question with that answer. Not sure why i was going the subquery route. Thanks

Collectives™ on Stack Overflow

MySQL SubQuery with query variables

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related