CODEWITHSUNDEEP
mysql
Zeus
Zeus

Reputation: 2253

Simple JOIN in SQL

I'm new to SQL thus the question. Here are my two tables.

persons
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| given_name  | varchar(30) | NO   |     | NULL    |                |
| family_name | varchar(30) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

courses

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| teacher_id | int(11)     | NO   | MUL | NULL    |                |
| name       | varchar(30) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

Here's my simple JOIN statement,

mysql> SELECT id, given_name, family_name FROM persons, courses WHERE courses.teacher_id = persons.id;

Here's what i get as an error,

ERROR 1052 (23000): Column 'id' in field list is ambiguous

What am I doing wrong here. Any help appreciated.

Upvotes: 0

Views: 49

Answers (2)

Want2bExpert
Want2bExpert

Reputation: 527

Include teacher foreign key in the person table to creat relationship between the teacher and the person. E.g. Many students is taught by a teacher

Upvotes: 0

Renzo
Renzo

Reputation: 27424

You should change your query in the following way:

SELECT persons.id, given_name, family_name 
FROM persons, courses 
WHERE courses.teacher_id = persons.id;

or 

SELECT courses.id, given_name, family_name 
FROM persons, courses 
WHERE courses.teacher_id = persons.id;

depending if you want the id of the course or that of the person.

Upvotes: 1

Related Questions