CODEWITHSUNDEEP
mysql
el_pup_le
el_pup_le

Reputation: 12179

MySQL join syntax unknown column

Why is sm.status_id and sm.user_id getting an unknown column error? I don't usually use the JOIN keyword for table joins.

...
FROM questions q, connections sm
JOIN users u ON q.user_id = u.id
JOIN users lu ON q.last_user = lu.id
JOIN (SELECT q2.id FROM questions q2 WHERE q2.status_id = sm.status_id LIMIT 2) x ON x.id = q.id
WHERE sm.user_id = 38
GROUP BY th.id

Upvotes: 0

Views: 70

Answers (2)

Taryn
Taryn

Reputation: 247810

You have a few issues with your current query.

First, you are mixing JOIN syntax. You have both comma separated values and then joins with an ON condition.

Second, you are attempting to access an alias for a table in an inner subquery which you cannot do.

My suggestion is to try something like this:

FROM questions q
JOIN users u 
    ON q.user_id = u.id
JOIN users lu 
    ON q.last_user = lu.id
JOIN 
(
    SELECT q2.id 
    FROM questions q2 
    JOIN connections sm
        ON q2.status_id = sm.status_id 
    WHERE sm.user_id = 38
    LIMIT 2
) x ON x.id = q.id
GROUP BY th.id

You did not post your SELECT list but if you want to include fields from the connections table in the select list then you will need to join on it again or include the fields in the subquery.

Upvotes: 2

Sashi Kant
Sashi Kant

Reputation: 13465

The reason is :: You need to use that columns in your select clause to make it visible inside the subquery like :

Select status_id ,sm.user_id
FROM questions q, connections sm
JOIN users u ON q.user_id = u.id
JOIN users lu ON q.last_user = lu.id
JOIN (SELECT q2.id FROM questions q2 WHERE q2.status_id = sm.status_id LIMIT 2) x ON x.id = q.id
WHERE sm.user_id = 38
GROUP BY th.id

Upvotes: 0

Related Questions