Retrive matching records from tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Retrive matching records from tables

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-04-06 : 04:54:25

Hi,
I have three tables AccountBase, Organisation and Clients
and I have AccountBaseID as a foreign key in Organisation and Clients tables..

Now i want information from AccountBase table for matching records in both tables Organisation and Clients
How can i do it with inner join? Is inner join with main tables will work?

I have written below query..using union.. Any other way using join to achieve it.


SELECT AB.Name, AB.AccountID
FROM [AccountBase] AS AB 
	 INNER JOIN tbl_Organisation O ON O.CRMAccountID = AB.AccountID
UNION ALL 
SELECT AB.Name, AB.AccountID
FROM [AccountBase] AS AB 
	 INNER JOIN tbl_ClientProjects CP ON CP.CRMAccountID = AB.AccountID

Sachin.Nand

2937 Posts

Posted - 2011-04-06 : 05:27:55

Can you post some sample data ?

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-04-06 : 05:41:32

AccountBase table


AID     Name
1D      A
2E      B
3F      C
43      D
85      F

Organisation


OID   OName   AID
1    test 1    1D
2    test 2    85

Client Project


CPID   CName   AID
1      cp1     3F
2      cp2     43

OUTPUT FOR MATCHED RECORDS IN LAST TWO TABLES..from AccountBase table.


AID    AName
1D     A
85     B
3F     D
43     F

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-06 : 06:08:57

But your output is not showing any sense
I think you are looking form this
Try this will Help you

select * from #AccountBase
where AId in
(select * from (select Aid from #ClientProject
union
select Aid from #Organisation) as t) order by AID

Raghu' S

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-04-06 : 06:14:11

Is there any other way using simply join instead of "in" for efficiency.

Sachin.Nand

2937 Posts

Posted - 2011-04-06 : 06:17:18

How are you getting 'B' for AName in o/p ?

PBUH

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-06 : 06:31:42

Ok Try this

select A.* from #AccountBase A, #ClientProject B,#Organisation C where A.Aid=B.Aid or A.Aid=C.Aid group by A.Name,A.AID

Raghu' S

Sachin.Nand

2937 Posts

Posted - 2011-04-06 : 06:42:50

quote:
Originally posted by raghuveer125

Ok Try this

select A.* from #AccountBase A, #ClientProject B,#Organisation C where A.Aid=B.Aid or A.Aid=C.Aid group by A.Name,A.AID

Raghu' S

Basically I feel using a OR in a join is a bad idea performance wise because the optmiser is going to scan
the each table independently to get the matching rows and then combine them to the final result.

PBUH

Sachin.Nand

2937 Posts

Posted - 2011-04-06 : 06:47:36

quote:
Originally posted by keyursoni85

Is there any other way using simply join instead of "in" for efficiency.

Just modify raghuvirs first query to this to use a join instead of IN.


select a.Name,a.AID from(
select * from #Organisation
union all
select * from #ClientProject
)t
inner join #AccountBase a on a.AID=t.AID

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-04-06 : 07:22:51

But above query will load all records from organisation and clientproject and then it will join with account base.. isn't it proper?
Confused..
I have currently used UNION QUERY as i posted above in first question.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-06 : 07:33:54

You are joining two time your tables with main table this is not good, performance wise.
Because your table have too short records so you wont see any performance issue.
When you deal with large records then you can see what is good for you.

Great carry on which one you want....!

Raghu' S

Sachin.Nand

2937 Posts

Posted - 2011-04-06 : 07:40:20

quote:
Originally posted by keyursoni85

But above query will load all records from organisation and clientproject and then it will join with account base.. isn't it proper?
Confused..
I have currently used UNION QUERY as i posted above in first question.

Difference between your query is that you are doing a set of operation(in this case inner join on a table) and then doing another set of operation on another table(again inner join) and then combining them using a union.

But with my query I am first combining the two tables and then joining them with the combined result.

If I give you 2 numbers 1 and 2 which I want that you add and divide it by 3 how will you go about this?

1) (1/3)+(2/3)---Method 1

2) (1+2)/3---Method 2

Which method will you adapt Method 1 or Method 2 ? I will go for Method 2.

Method 1 is what your query does while Method 2 is what my query does.

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-04-06 : 07:47:43

Yes very true..
thank you very much..sachin and raghu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources