'Unknown table' although it really exists

Question

$query = mysql_query("SELECT company.*, factory.*
                      FROM company
                      FULL JOIN factory
                      ON company.n_id = factory.n_id
                      WHERE company.n_id = '$n_id' AND factory.n_id = '$n_id'") or die(mysql_error());

Above you see my query. It gives me Unknown table 'company' error, but company table exists 100%. What am I missing?

Edit: When i use right join or left join instead of full join, it works... But I need full join. Omg, I will shoot somebody.

Looks good to me. Maybe you have a typo in the table name in your database. — Felix Kling, Jun 20 '10 at 17:38
On a side note, I'd advise you to move to the mysqli extension. — Artefacto, Jun 20 '10 at 17:38
What does `SELECT COUNT(*) FROM company` return when run from the command line client or the query browser? — Mark Byers, Jun 20 '10 at 17:38
Are you connected to correct database? Try simple query first. — Nik Chankov, Jun 20 '10 at 17:40
Have you tried a sample query (with the variable filled in) directly in your MySQL client/command line? This would help in figuring out whether the error lies at the PHP or the SQL level. — Steven, Jun 20 '10 at 17:48
Usually I see this error when I alias one of the table names, e.g. `company c`, and forget that I can only refer to it as `c` from now on. If this is the exact query you're running, though, that shouldn't be it. — Matchu, Jun 20 '10 at 17:49
According to http://dev.mysql.com/doc/refman/5.1/en/join.html, the `FULL` keyword **doesn't exist in MySQL**. — MvanGeest, Jun 20 '10 at 17:52
I couldn't find docs on FULL JOIN in MySQL, either. How come MySQL doesn't give a syntax error, though? — JAL, Jun 20 '10 at 17:55

score 2 · Accepted Answer · answered Jun 20 '10 at 17:52

There's no FULL OUTER JOIN in MySQL - see this link for how to get the functionality.

$query = mysql_query("     SELECT * FROM COMPANY c
                       LEFT JOIN FACTORY f ON f.n_id = c.n_id
                      UNION ALL
                          SELECT * FROM COMPANY c
                      RIGHT JOIN FACTORY f ON f.n_id = c.n_id
                           WHERE c.? IS NULL
                             AND c.n_id = mysql_real_escape_string($n_id)
                             AND f.n_id = mysql_real_escape_string($n_id)") or die(mysql_error());

You need to update the ? to be the name of a column from the COMPANY table, other than n_id.

@hey: Check the link for alternative queries to produce the same result, but it doesn't change the fact that MySQL doesn't support the FULL keyword. — OMG Ponies, Jun 20 '10 at 18:41

score 0 · Answer 2 · answered Jun 20 '10 at 17:51

I replaced the tables and columns with my own and ran the query, and had the same errors. Removing different parts of the query gave me more errors relating to columns or tables not existing. I don't have an explanation for that, possibly because I have never used a FULL JOIN.

I found that changing FULL JOIN to another type of join fixed the issue. Try changing FULL JOIN to JOIN or RIGHT JOIN and see if those results do what you want.

Edit: Oh, I see your comment about needing FULL JOIN now. Could you rewrite the query with UNION instead?

I need full join, because some tables can have matches some don't — good_evening, Jun 20 '10 at 17:53

'Unknown table' although it really exists

2 Answers2