CODEWITHSUNDEEP
mysqlsqljoin
bob_cobb
bob_cobb

Reputation: 2269

Field list in ambiguous in join with 4 tables

Very confused why this is happening since I'm naming the specific tables as es and sub that it's complaining about (and I think that's how you fix that particular issue).

I've got: users submissions_comments email_settings submissions tables

This query gives me what I want, but I now want to join this on my email_settings and submissions table which both have a user_id.

SELECT user_id,
       submission_id,
       comment,
       users.*,
FROM submissions_comments
INNER JOIN
  (SELECT submissions_comments.parent_id
   FROM submissions_comments
   WHERE id = 224) x ON submissions_comments.id = x.parent_id
LEFT JOIN users ON submissions_comments.user_id = users.id

Output:

       user_id: 35
 submission_id: 12
       comment: fdasadfsdadfs
            id: 35
         email: [email protected]
      username: bobcobb
          name: Robert Cobb
         about:
       created: 2014-03-21 20:24:57
    last_login: 2014-07-06 23:21:43
public_profile: 1
  queued_photo: 0

But if I try to join on another table (which has a reference to a user_id) I get Column 'user_id' in field list is ambiguous

SELECT user_id,
       submission_id,
       comment,
       users.*,
       es.*,
       sub.*
FROM submissions_comments
INNER JOIN
  (SELECT submissions_comments.parent_id
   FROM submissions_comments
   WHERE id = 224) x ON submissions_comments.id = x.parent_id
LEFT JOIN users ON submissions_comments.user_id = users.id
LEFT JOIN submissions sub ON x.parent_id = sub.user_id
LEFT JOIN email_settings es ON x.parent_id = es.user_id;

As you can tell I'm joining all of these on the user_id returned from the inner select (e.g. x.parent_id).

Upvotes: 0

Views: 39

Answers (2)

ah_hau
ah_hau

Reputation: 768

SELECT submissions_comments.user_id,
       submissions_comments.submission_id,
       submissions_comments.comment,
       users.*,
       es.*,
       sub.*
FROM submissions_comments
INNER JOIN
  (SELECT submissions_comments.parent_id
   FROM submissions_comments
   WHERE id = 224) x ON submissions_comments.id = x.parent_id
LEFT JOIN users ON submissions_comments.user_id = users.id
LEFT JOIN submissions sub ON x.parent_id = sub.user_id
LEFT JOIN email_settings es ON x.parent_id = es.user_id;

you need to define which user_id you are referring on the select part as well.

Upvotes: 0

Jens
Jens

Reputation: 69470

You have to add the alias to the column name:

SELECT sub.user_id,...

Upvotes: 2

Related Questions