join statement question

[habesha]

Hi,

I have three table
say
tbl1
col1 col2
1 1
2 1
3 2
4 3
tbl2
col1 col2
1 aa
2 bb


tbl3
col1 col2
1 dd
2 ee
3 ff

col2 of tbl1 is a foreign key refering to col2 of both tbl2 and tbl3

I want to select all records from tbl1 when col2 of tbl1 exists either in tbl1 or tbl2 or in both, if it is not existing in both tables, I will not select that specific record
there for how can I write an inner join statement to accomplish this

Thanks

 

[traingamer]

Use Inner Join for each of the joins, then constrain with a where clause

...
where ((tbl3.col2 is not null) AND (tbl2.col2 is not null))

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[gmmastros]

Try this...

Select  * 
From    Tbl1
        Left Join Tbl2
          On Tbl1.Col1 = Tbl2.Col1
        Left Join Tbl3
          On Tbl1.Col1 = Tbl3.Col1
Where	Coalesce(Tbl2.col1, Tbl3.col1) is not null

You will need to use a left join for the condition where a record can be found in tbl2 (or tbl3) without a record in the other table.

The Coalesce where clause makes it such that if the record does not exist in either table, it will be filtered out.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[habesha]

you mean

select tbl1.*
from tbl1 inner join tbl2
on tbl1.col2 = tbl2.col1
inner join tbl3
on tbl1.col2 = tbl3.col1
where ((tbl3.col2 is not null) AND (tbl2.col2 is not null))


this code is not giving me the right recordset
col1 col2
1 1
2 1
3 2
4 3

the query should check the existance of col2 of tbl1 in col1 of either tables and extract the record

 

[gmmastros]

Did you try my suggestion. (hint: scroll up a little.)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[traingamer]

I meant to say left join as well. That'll teach me to cut and paste...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[habesha]

Thank you
george's suggestions works fine

Thank you I appreciate that