Query - General - LEFT JOIN problem

I have 3 tables:

test1
id name1
1 value1

test2
id name2
1 value2

test3
id name3
3 value3_01
4 value3_02

I want to display all records of table test1 with values of table test2 (test1.id = test2.id) and values of test3 (if test.id match with test1.id) - in this case test3 has no common ids with test1 so NULLs are displayed.
How to make sql query to display:
id1 name1 name2 name3
1 value1 value2 NULL

I constructed query:
SELECT t1.id as id1, t1.name1 as name1, t2.name2 as name2, t3.name3 as name3
FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)
ON (t1.id=t2.id and t1.id=t3.id)

but it gives me:

id1 name1 name2 name3
1 value1 NULL NULL

name2 is NULL instead of desired "value2". WHY?

LEFT JOIN DEFINITION:
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table, even if there are no matches in the right table.

There are no matches in test3 table when joining test1 table so name3 from table3 is NULL in result.

BUT There are matches in table test2 (test2.id=1 and test1.id=1) so why null is displayed?

Hope anyone can help me. I am in big trouble.

Thanks in advace.
Tom

This query displays all records from table1 with other 2 tables each joined to table1 to retrieve those table fields.

SELECT Table1.ID, Table1.Name1, Table2.Name2, Table3.Name3
FROM Table3 RIGHT JOIN (Table2 RIGHT JOIN Table1 ON Table2.ID = Table1.ID) ON Table3.ID = Table1.ID;

Result is:
id1 name1 name2 name3
..1 value1 value2

Hi

I managed it:

SELECT t1.id as id1, t1.name1, t2.name2, t3.name3
FROM test1 t1
LEFT JOIN test2 t2 ON t1.id=t2.id
LEFT JOIN test3 t3 ON t1.id=t3.id


now when t2.id is found in t1 it displays

1 value2 NULL

but if t2.id not found in t1 it displays
1 NULL NULL

earlier my query displayed 1 NULL NULL even if t2.id existed in t1 table, because I was somehow inner joining t2.id nad t3.id:

FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)
ON (t1.id=t2.id and t1.id=t3.id)

which means that to results to test2 and test3 columns go values where t1.id=t2.id and t1.id=t3.id ant it is the same as t2.id=t3.id


thanks anyway

HI , I hope that the below query will be very useful for you..




Thanks & Regards
Sakthimeenakshi.S