mysql join problem

[savvas]

i need to run a select statement in which there are two conditions each applying in one of the two tables i am selecting from. however sometimes the second table is empty and hence the query returns nothing. I was thinking that as i was using an OR statement for the two queries I should had at least the results from the first table. am i wrong assuming so?

eg:

select * from t1, t2 where t1.id=1 OR t1.id=t2.id

if t2 is empty i get nothing

also i get nothing for the following:
select * from t1, t2 where t1.id=1

is it the same problem?

thnx

Savvas

[quelle]

A JOINed table creates a new table out of two tables where each row consists of a row from the first table and a row in the second table, linked together by a common primary key, like your ID field.

By default, a JOIN in mysql will only join rows where the primary key values are NOT NULL. This sometimes won't work for you since t2 is sometimes empty and therefore it contains no rows with a primary key value to be matched in t1.

If you specify a LEFT JOIN, then the JOIN is performed for EVERY row in t1, whether or not a matching row exists in t2. If t2 is empty or there are no rows that match (based on your join condition) then mysql will join that t1 row with an imaginary t2 row where all the values are NULL.

Try this:

[b]select * from t1 left join t2 on t1.id = t2.id  where t1.id=1;[/b]

If t2 is empty, this should yield something like the following:
+----+--------+------+------+
| id | data   | id   | data |
+----+--------+------+------+
|  1 | data1a | NULL | NULL |
+----+--------+------+------+

Hope this helps! 🙂