select data from 3 tables (join) REGARDLESS of one table not containing a row

Question

select data from 3 tables (join) REGARDLESS of one table not containing a row

tsqlnerd

Newbie

Points: 1
More actions
January 6, 2010 at 8:14 am

#218088

Here is my dilemma. I have three tables that I need to join together but at any given time any one of the tables could return 0 results. It seems like I need to left join a to b and left join b to a, left join b to c and c to b, left join a to c and c to a. How can I achieve this syntactically?
For example,
select INULL(TotalWidgets,0) + IFNULL(TotalCogs,0) + ISNULL(TotalSprockets,0) Total
from
(
select sum(widgets) TotWidget, CompanyID
from Widgets
) a FULL JOIN b
(
select sum(cogs) TotCogs, CompanyID
from Cogs
) b ON (a.CompanyID = b.CompanyID) FULL JOIN
(
select sum(sprockets) TotSprockets, CompanyID
from Sprockets
) c ON (a.CompanyID = b.CompanyID AND a.CompanyID = c.CompanyID)
Also, this is a basic sketch of the query because it is alot more complex and intricate.
I need what is in a regardless of whether b and c return any values, and vice versa. Also, they may all return values. Currently if one of the derived tables returns no records, it makes Total return null. Also, I am thinking of FULL JOIN because it seems like I need to do a LEFT an RIGHT join at the same time. Is there a way I can avoid a full join as well?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would use a UNION to get all possible CompanyID as the left side of the joins and join all three other tables to that:

;WITH cte as

(

SELECT CompanyID

from Widgets

UNION

SELECT CompanyID

from Cogs

UNION

SELECT CompanyID

from Sprockets

)

select ISNULL(TotalWidgets,0) + ISNULL(TotalCogs,0) + ISNULL(TotalSprockets,0) Total

FROM cte c

LEFT OUTER JOIN Widgets w on c.CompanyID = w.CompanyID

LEFT OUTER JOIN Cogs co on c.CompanyID = co.CompanyID

LEFT OUTER JOIN Sprockets s on c.CompanyID = s.CompanyID

Edit:

... and if I have to use that join frequently I might think about a more normalized database structure... At least a separate table holding ALL CompanyIDs...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 2

I agree with Lutz that you are fighting a poor database design here, however:

DECLARE @Cogs TABLE (company_id INT PRIMARY KEY, cogs INT NOT NULL);

DECLARE @Sprockets TABLE (company_id INT PRIMARY KEY, sprockets INT NOT NULL);

DECLARE @Widgets TABLE (company_id INT PRIMARY KEY, widgets INT NOT NULL);

INSERT @Cogs VALUES (1, 3), (4, 2);

INSERT @Sprockets VALUES (2, 3), (3, 2);

INSERT @Widgets VALUES (3, 3), (5, 2);

WITHUnioned (company_id, quantity)

AS(

SELECTcompany_id, cogs

FROM@Cogs

UNIONALL

SELECTcompany_id, sprockets

FROM@Sprockets

UNIONALL

SELECTcompany_id, widgets

FROM@Widgets

)

SELECTcompany_id, total = SUM(quantity)

FROMUnioned

GROUPBY company_id

ORDERBY company_id;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi