SQL unknown column when doing join

I have been getting an erro开发者_运维问答r "Unknown column 'guests_guest.id' in 'field list'" when i try to run the following:

SELECT guests_guest.id
FROM `guests_guest` full join
guests_guest_group
on guests_guest.id=guests_guest_group.guest_id

All the column& table names are correct. in fact, running just

SELECT guests_guest.id
FROM `guests_guest`

works just fine. I suspect there is a syntax issue I am missing. what am I doing wrong?

try:

SELECT gg.id
FROM `guests_guest` as gg
join guests_guest_group as ggg
on ggg.guest_id=gg.id

assuming guests_guest_group does not have an id column.

full join ?

Have you tried simply removing the full?

This not Oracle, it's MySQL, right? AFAIK, FULL JOIN is not implemented yet in MySQL.

The parser (because "full' is not a keyword it knows), evaluates your query as:

SELECT guests_guest.id
FROM guests_guest AS full                 <--- crucial note
  JOIN guests_guest_group
    ON guests_guest.id = guests_guest_group.guest_id

After that, guests_guest is not a name it knows, but it uses full as an alias for table guests_guest. That's why this error is produced.

If you really need FULL JOIN and not (INNER) JOIN, then search SO for how to implement FULL JOIN in MYSQL.

@rockerest: I guest so.

Two things I'd look at:

1. Spelling... I am a fast typer, but sometimes my fingers are dyslexic. Worst case, do a describe on each table and check column names against each other. Or, use the system-confessed column names and copy/paste.

2. Aliases... but, someone else has mentioned that.

So I guessed just one thing.