2

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".

Community
  • 1
  • 1
Adn
  • 29
  • 1
  • 3
  • 6

10 Answers10

8

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
Nicole
  • 32,841
  • 11
  • 75
  • 101
  • Both are INNER JOINs - the first is ANSI-89 syntax, the later is ANSI-92. Both queries should be using table aliases, unless you like re-typing full table names for every reference... – OMG Ponies Jul 13 '11 at 15:52
1

The problem is with your WHERE statment, you need to use:

where student_info.id=1

or:

where student_class.id=1
jeroen
  • 91,079
  • 21
  • 114
  • 132
0
SELECT Name,class 
FROM student_info,student_class 
WHERE student_info.sb_id=1 AND student_info.sb_id=student_class.sb_id;
JohnFx
  • 34,542
  • 18
  • 104
  • 162
CatchingMonkey
  • 1,391
  • 2
  • 14
  • 36
0

SELECT a.Name,a.class from student_info as a join student_class as b on a.sb_id = b.sb_id where id=1;

ethrbunny
  • 10,379
  • 9
  • 69
  • 131
0

Should your where id=1 be where student_info.sb_id = 1 ?

You could change it to a join like this:

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

Jeff Lambert
  • 24,395
  • 4
  • 69
  • 96
0
SELECT Name,class from student_info join student_class on student_info.sb_id=student_class.sb_id where student_info.id=1;
DGM
  • 26,629
  • 7
  • 58
  • 79
0
SELECT student_info.Name,student_info.class from student_info inner join student_class on student_info.sb_id=student_class.sb_id where student_info.id=1;
simnom
  • 2,590
  • 1
  • 24
  • 34
0

this means id column is present in both tables, just replace the id with student_info.id or student_class.id which ever is intended

Muhammad Usama
  • 2,797
  • 1
  • 17
  • 14
0

Prefix the ID column in the where clause with the table name. It is confused because ID is in both tables and it doesn't know which one you mean.

WHERE student_info.id=1

or

WHERE student_class.id=1

depending on which one you meant when you wrote the query.

JohnFx
  • 34,542
  • 18
  • 104
  • 162
0

It's because both of your tables have id field, so you need to specify which id you want to use (you may or may not use table aliases, I prefer to use, but it's really up to you). Also, using JOIN for joining tables is much better practice than putting everything in FROM. I'd rewrite your query to 

SELECT Name,class 
from student_info si
INNER JOIN student_class sc ON (sc.sb_id = si.sb_id)
WHERE si.id = 1  // or sc.id =1, whatever makes sense
a1ex07
  • 36,826
  • 12
  • 90
  • 103