Joining three tables - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Joining three tables

Author

Topic

evikjames
Starting Member

1 Post

Posted - 2002-09-09 : 18:49:01

I need to display three columns from data from three tables. The first column is "Franchisors"; the second column is "Referrals", which is a sum; the third column is "Deals", which is also a sum. All tables have a FranchisorID.

I currently have a query that will get me the first two columns. I need to know how to add join in the third table. Can you help? My current query looks like this:

SELECT F.FranchisorID, F.Franchisor,
COUNT(F.FranchisorID) AS TotalReferrals
FROM Franchisor F LEFT JOIN LeadFranchisorJunction LFJ ON F.FranchisorID = LFJ.FranchisorID

WHERE F.FranchisorID IN ( #FranchisorList# )

My table should look like this:

Franchisor Referrals Deals
Me 1 2
You 3 5
Us 4 4

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-09 : 19:46:43

This is sorta hard for me to do without the actual CREATE TABLE statements, but here goes:

 
SELECT F.FranchisorID, F.Franchisor, 
COUNT(F.FranchisorID) AS TotalReferrals,
COUNT(d.DealID) as TotalDeals

FROM Franchisor F 
LEFT JOIN LeadFranchisorJunction LFJ ON F.FranchisorID = LFJ.FranchisorID 

LEFT JOIN Deals d ON d.FranchisorID = F.FranchisorID 

WHERE F.FranchisorID IN ( #FranchisorList# )

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

r937
Posting Yak Master

112 Posts

Posted - 2002-09-09 : 19:49:18

is there a relationship between LeadFranchisorJunction and Deal?

rudy

r937
Posting Yak Master

112 Posts

Posted - 2002-09-09 : 19:54:14

the reason i ask is because if there is no such relationship, then making a three-way join will surely have a cross-join effect (at least, within each FranchisorID), leading to inflated counts... which i have seen people resort to DISTINCT to avoid... which may work but it's inefficient

perhaps two separate queries will work --

select a, count(*) as countb from tableA join tableB

as query1 will give one count, while

select a, count(*) as countc from tableA join tableC

as query2 will give the other count, and then you can go

select a, counta, countb
from ( query1 ) join ( query2 )

if you follow my pseudo-sql

rudy
http://rudy.ca/

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2002-09-10 : 14:34:29

Here is a sample solution that you can work with which may help in your situation. This sample uses Derived Tables to Calculate Aggregate Values.

http://www.sqlteam.com/item.asp?ItemID=6692

create table table1(keyid int)
go
insert into table1 values(1)
insert into table1 values(1)
insert into table1 values(2)
insert into table1 values(3)
insert into table1 values(3)
go
create table table2 (keyid int)
go
insert into table2 values(1)
insert into table2 values(1)
insert into table2 values(2)
insert into table2 values(3)
insert into table2 values(4)
go

select * from table1
select * from table2

SELECT a.keyid, Isnull(b.Tab1,0) as 'count_table1', Isnull(c.Tab2,0) as 'count_table2'
FROM (select DISTINCT keyid from table1) a
LEFT JOIN (select keyid,count(1) as Tab1 from Table1 group by keyid) b
ON a.keyid = b.keyid
LEFT JOIN (select keyid,count(1) as Tab2 from Table2 group by keyid) c
ON a.keyid = c.keyid
WHERE a.keyid in (1,2,3)

Edited by - ehorn on 09/10/2002 15:05:36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources