CODEWITHSUNDEEP
mysqlsqlmysql-error-1052
Marco Pietro Cirillo
Marco Pietro Cirillo

Reputation: 864

Inner Join from multiple tables

I have 3 tables, as follows:

Patron
======
patron_num


Booking_For_Schedule
====================
tname
date
time
booking_num

Booking_By_Patron
=================
booking_num
patron_num

I would like to retrieve a result with columns patron_num, date, time, and tname, like so:

patron_num     date      time     tname
     1      2013-11-03  20:00    TestName

...etc

The purpose of this homework question is to teach us INNER JOINS, but I am having some difficulty. Could some kind SO user push me in the right direction?

Here's my SQL:

SELECT `patron_num`,`date`,`time`,`tname`
FROM `booking_for_schedule` `F`
INNER JOIN `booking_by_patron` `B` on `F`.`booking_num` = `B`.`booking_num`
INNER JOIN `patron` `P` on `B`.`patron_num`=`P`.`patron_num`

which returns the error: #1052 - Column 'patron_num' in field list is ambiguous

Upvotes: 1

Views: 320

Answers (2)

Abe Miessler
Abe Miessler

Reputation: 85046

Since patron_num is in multiple tables you need to specify which one you want to use. You can do this by adding the table alias before it like you are in your joins. Example:

SELECT `B`.`patron_num`,`date`,`time`,`tname`
FROM `booking_for_schedule` `F`
INNER JOIN `booking_by_patron` `B` on `F`.`booking_num` = `B`.`booking_num`
INNER JOIN `patron` `P` on `B`.`patron_num`=`P`.`patron_num`

Upvotes: 1

Richard Brown
Richard Brown

Reputation: 11436

SELECT `P`.`patron_num`,`date`,`time`,`tname`
FROM `booking_for_schedule` `F`
INNER JOIN `booking_by_patron` `B` on `F`.`booking_num` = `B`.`booking_num`
INNER JOIN `patron` `P` on `B`.`patron_num`=`P`.`patron_num`

Will help with the ambiguity.

Upvotes: 1

Related Questions