Inner joining one table against many others

I have a table A with columns (Id,Value)

and a table B with Columns (BId,Id,..) and a table C with columns (CId,Id,...)

I need to perform an inn开发者_开发问答er join on these tables as follows

 select a.Id,a.Value from A a
 inner join B b on b.Id=a.Id
 inner join C c on c.Id=a.Id
 where <many conditions on table B and C>

How can i achieve the same. Now when i just run the query

 select a.Id,a.Value from A a
 inner join B b on b.Id=a.Id
 inner join C c on c.Id=a.Id

it doesnt return anything.. please help.

FYI when i run the joins separately it gives me the rows. I just want a union of them...

Sample data:

A

1

2

3

B

2

C

3

then i want to select

A

2

3

Thanks in advance.

So, following your comments, it appears that you want something like this:

select a.Id,a.Value from A a
 inner join B b on b.Id=a.Id
where <many conditions on table B>
UNION ALL
SELECT a.Id, a.Value from A
 inner join C c on c.Id=a.Id
 where <many conditions on table C>

As long as the fields match on ID from A -> B and A -> C and you dont have any other join condition, you should be able to see the matching rows.

I could not understand your point about how the B and C Id do not match. if a.id=b.id and a.id=c.id, Doesn't it automatically imply b.id = c.id?

Anyways, in situations like these, I try to do outer join of A on B and C and see if the rows that I think are matching in fact do exist.

select a.Id,a.Value from A a
 left outer join B b on b.Id=a.Id
 left outer join C c on c.Id=a.Id
 where (b.id is not null or c.id is not null) 
       /* Matching record found in b or c */

EDIT: Based on your requirement, you can use the approach that Lamak suggested above (Using UNION Alls) or if you are certain that for each record in A, you will only have one record in B and one in C at most and only one column, you can use the scalar sub query approach.