JOIN problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

JOIN problem

I have three tables. Table1 :
——————————
| ID | data1 | data2 | data3 |
—————————— Table2 :
—————
| ID | data10 |
————— Table3 :
—————
| ID | data20 |
————— If I submit the query which is "select Table1.ID from Table1, Table2, Table3"
sometimes it gives me results, sometimes not.
I figure out that if one of Table1, Table2 and Table3 has no data in the table, it give me no results for the query. any idea to solve this problem?
Hi Jason, you’ll need to use outer joins to achieve this e.g. select table1.id
from table1
left outer join table2
on table2.id = table1.id
left outer join table3
on table3.id = table1.id This allows for records to not exist in either table2 or table3 or both Cheers
Twan
I can´t follow you.
With your select you are doing cartesian join.
What result do you want to have?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Sorry, while I was thinking, Twan give the exactly answer.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
too slow, I win! <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br />
sorry to make you confused. Table1 :
——————————
| ID | data1 | data2 | data3 |
——————————
| 1 | aaaa1 | aaaa2 | aaaa3 |
| 2 | bbbb1 | bbbb2 | bbbb3 | Table2 :
—————
| ID | data10 |
—————
| 1 | gggggg |
| 2 | hhhhhh | make it simple, I’ll go with two tables. if data10 has "gggggg", I want the ID from Table2
with that ID, I want to retrieve in Table1 such as "aaaa1", "aaaa2"

Select data1,data2
from Table1 T1
join Table2 T2
on T1.id = T2.id
where T2.data10 = ‘gggggg’
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
]]>