Was: Not unique table :: Now: #1054 - Unknown column - can't understand why?

Question

I'm trying to join some tables together in MySQL, but I seem to get an error saying: #1066 - Not unique table/alias: 'calendar_jobs'

I really want it to select everything from the cal_events, the 2 user bits and just the destination col from the jobs table, but become "null" if there arn't any job. A right join seemed to fit the bill but doesn't work! Can anyone help!?

UPDATE:

Thanks for the help on the previous query, I'm now up to this:

SELECT calendar_events.* , calendar_users.doctorOrNurse, calendar_users.passportName, calendar_jobs.destination
FROM `calendar_events` , `calendar_users`
RIGHT JOIN calendar_jobs ON calendar_events.jobID = calendar_jobs.jobID
WHERE `start` >= 0
AND calendar_users.userID = calendar_events.userID;

But am now getting an error saying: #1054 - Unknown column 'calendar_events.jobID' in 'on clause'

What is it this time!?

Thanks again!

Answer 1

You shouldn't use calendar_jobs in the FROM clause, because you've already specified it in the JOIN. Try this:

SELECT calendar_events.* , calendar_users.doctorOrNurse, calendar_users.passportName, calendar_jobs.destination
FROM `calendar_events` , `calendar_users`
RIGHT JOIN calendar_jobs ON calendar_events.jobID = calendar_jobs.jobID
WHERE `start` >=0
AND calendar_users.userID = calendar_events.userID

---

Answer for update:

All evidence seems to indicate that the column doesn't exist in that table :).

Try this:

SELECT calendar_events.* , calendar_users.doctorOrNurse, calendar_users.passportName, calendar_jobs.destination
FROM `calendar_users`, `calendar_events`
RIGHT JOIN calendar_jobs ON calendar_events.jobID = calendar_jobs.jobID
WHERE `start` >=0
AND calendar_users.userID = calendar_events.userID

The order of the tables in the FROM has been switched, because you join events with jobs.

Answer 2

Run this: show create table calendar_events; , and post the results here.

We need to see the table structure to answer your second question.

Answer 3

You have calendar_jobs listed twice in your query.

Once on the second line and once on the RIGHT JOIN statement.