How can I resolve an ambiguous column name error in MySQL?

Question

Possible Duplicate:
1052: Column 'id' in field list is ambiguous

I have two tables, and I want to connect the tables by sb_id (sb_id is same for two tables). So, I used my query like this:

SELECT Name, 
       class 
  FROM student_info,
       student_class 
 WHERE id = 1 
   AND student_info.sb_id = student_class.sb_id;

And it's showed error:

1052: 'id' in where clause is ambiguous

Another thing,I want to show just a single result,by using "JOIN".

Answer 1

That means that both tables have id and you need to prefix it with the table that it appears in (if it is the same in both, either will do).

SELECT
  name,
  class
FROM student_info, student_class
WHERE
  student_info.id=1
  AND student_info.sb_id=student_class.sb_id;

This will return a single result, as you desire, as long as there is exactly one record with student_info.id=1 and a student_class record with a matching sb_id. The result is the same as if you used INNER JOIN — in other words, both records must exist and are joined together.

The corresponding INNER JOIN syntax would look like this:

SELECT
  name,
  class,
FROM student_info
INNER JOIN student_class ON student_info.sb_id = student_class.sb_id
WHERE student_info.id = 1