CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Michelle
Michelle

Reputation: 1

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 inner 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.

Upvotes: 0

Views: 580

Answers (2)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

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.

Upvotes: 0

Lamak
Lamak

Reputation: 70638

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>

Upvotes: 2

Related Questions