3 tables, left join, in case DATA is missing in ONE table

table1 (ids always exist)

+----+------+
| id | col1 |
+----+------+
| 1  |  ab  |
+----+------+
| 2  |  gh  |
+--开发者_运维百科--+------+

table2 (ids always exist)

+----+------+
| id | col2 |
+----+------+
| 1  |  cd  |
+----+------+
| 2  |  ij  |
+----+------+

table3 (ids might be missing, in this case 2 is missing)

+----+------+
| id | col3 |
+----+------+
| 1  |  ef  |
+----+------+

PHP

$col = 'ab';

$a = mysql_query("SELECT t1.id FROM table1 AS t1, table2 AS t2, table3 AS t3
WHERE t1.id = t2.id AND t2.id = t3.id AND (t1.col1 = '$col' OR t2.col2 = '$col'
OR t3.col3 = '$col) GROUP BY t1.id, t2.id, t3.id");

That would only work if all three tables had "the same id" included, but what happens if an "id" is missing in table3 for whatever reason? how can I still test for all three tables and get t1.id to output 1, when $col = ab? would I have to use left join?

$a = mysql_query("SELECT t1.id FROM table1 AS t1, table2 AS t2 
LEFT JOIN (SELECT id FROM table3 WHERE col3 = '$col') AS t3 ON t3.id = t1.id 
WHERE t1.id = t2.id AND (t1.col1 = '$col' OR t2.col2 = '$col')
GROUP BY t1.id, t2.id");

what am I doing wrong here?

What are you doing wrong? Querying a table that doesn't exist. That's always going to raise an error.

I'm not going to address the wisdom of designing a database in which tables crucial to your queries come and go.

Your only hope on the client side is to

• test for the existence of the tables you're interested in, and
• execute different SQL statements based on those results.

It sounds like you need either one or two left outer joins. This gives you all the ids that are common to both table1 and table2, regardless of whether they're in table3.

select t1.id, t2.id, t3.id
from table1 t1
inner join table2 t2 on (t1.id = t2.id)
left join table3 t3 on (t1.id = t3.id);

And this gives you all the ids that are in table1, regardless of whether they're in table2 or table 3.

select t1.id, t2.id, t3.id
from table1 t1
left join table2 t2 on (t1.id = t2.id)
left join table3 t3 on (t1.id = t3.id);

And, of course, you can filter the results with your WHERE clause.

LEFT JOIN only works when the table DOES exist, but contains no data.

If you don't want to have to do stuff like Catcall suggested (check if the table exists, and use different SQL statements based on that...) you have to make sure that the table exists in the database, even if it's completely empty.

Under normal circumstances, you should have a certain degree of control over your application's database, so you should be able to make sure that the table is really there.

If this is really a big problem (like, that you can't be sure if someone deleted the table) you could check this every time you start your application: create the table if it doesn't exist.