Show all data from four tables joined or matched on a particular column

Question

Show all data from four tables joined or matched on a particular column

munta

Mr or Mrs. 500

Points: 569
More actions
February 23, 2013 at 2:48 pm

#270784

Hello all.
I am trying to select computer names from a number of tables and display them ordered. I have been looking at outer joins but not having much success.
The requirement is to display every computer name from each table and match them like the example below.
Tbl1Tbl2Tbl3Tbl4
comp1Nullcomp1comp1
comp2comp2comp2comp2
comp3comp3comp3comp3
comp4comp4comp4Null
comp5NullNullNull
comp6Nullcomp6comp6
Nullcomp7NullNull
Nullcomp8NullNull
Nullcomp9NullNull
Nullcomp10NullNull
Jason.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

To help those who want to assist you ... can you post the table definitions, some sample data.

To do this quickly and easily please click on the first link in my signature block, read the article by Jeff Moden.. by the way the aricle contains the T-SQL statements that will allow you to post what I have requested simply and easily.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 2

you might want to look into a FULL JOIN of all four tables.

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]

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

SELECT

COALESCE(t1.comp_name, t2.comp_name, t3.comp_name, t4.comp_name) AS comp_name,

...

FROM dbo.Tbl1 t1

FULL OUTER JOIN dbo.Tbl2 t2 ON

t2.comp_name = t1.comp_name

FULL OUTER JOIN dbo.Tbl3 t3 ON

t3.comp_name = t1.comp_name

FULL OUTER JOIN dbo.Tbl4 t4 ON

t4.comp_name = t1.comp_name

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

munta Mr or Mrs. 500 Points: 569 More actions · Answer 4

Thanks to all those who replied.

bitbucket-25253, I would like to give you more but two of the four are not actual tables exactly. One is a call to Active Directory. Another is from an Excel Spreadsheet. Yet another is from a view in SCCM and lastly one is from a table from another server?

A computer should have a record in all of the above. I am trying to find computer names that don't exist in one or more of these data sources. full outer joins seemed logical and I think I have found the answer after some initial testing. Further testing later this week will hopefully prove me correct.

LutzM, ScottPletcher.

Thank you. I was looking at Full Outer Join and finally found that I needed more than one join definition. (if thats what it is called) I of course want to fully test with some dummy data to prove why but at the moment the results look correct.

If a device is missing from any of the tables a row is returned showing Null where the device is missing from.

Suedo Code

Table1

full outer join

Table2

on table1.computername = table2.computername

full outer join table3

on table1.computername = table3.computername

or table2.computername = table2.computername

full outer join table4

on table1.computername = table4.computername

or table2.computername = table4.computername

or table3.computername = table4.computername

where table1.Name is null

or table2.name is null

or table3.name is null

or table4.name is null

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

Hmmm... All those ORs in the ON clauses looks pretty messy. Here's an alternate way but I haven't tested to see if it performs better:

DECLARE @T1 TABLE (comp_name VARCHAR(10))

DECLARE @T2 TABLE (comp_name VARCHAR(10))

DECLARE @T3 TABLE (comp_name VARCHAR(10))

DECLARE @T4 TABLE (comp_name VARCHAR(10))

INSERT INTO @T1

SELECT 'comp1' UNION ALL SELECT 'comp2' UNION ALL SELECT 'comp3'

UNION ALL SELECT 'comp4' UNION ALL SELECT 'comp5' UNION ALL SELECT 'comp6'

INSERT INTO @T2

SELECT 'comp2' UNION ALL SELECT 'comp3' UNION ALL SELECT 'comp4'

UNION ALL SELECT 'comp7' UNION ALL SELECT 'comp8' UNION ALL SELECT 'comp9'

UNION ALL SELECT 'comp10'

INSERT INTO @T3

SELECT 'comp1' UNION ALL SELECT 'comp2' UNION ALL SELECT 'comp3'

UNION ALL SELECT 'comp4' UNION ALL SELECT 'comp6'

INSERT INTO @T4

SELECT 'comp1' UNION ALL SELECT 'comp2' UNION ALL SELECT 'comp3'

UNION ALL SELECT 'comp6'

--Tbl1Tbl2Tbl3Tbl4

--comp1Nullcomp1comp1

--comp2comp2comp2comp2

--comp3comp3comp3comp3

--comp4comp4comp4Null

--comp5NullNullNull

--comp6Nullcomp6comp6

--Nullcomp7NullNull

--Nullcomp8NullNull

--Nullcomp9NullNull

--Nullcomp10NullNull

;WITH AllComputers AS (

SELECT comp_name FROM @T1 UNION

SELECT comp_name FROM @T2 UNION

SELECT comp_name FROM @T3 UNION

SELECT comp_name FROM @T4)

SELECT a.comp_name

,T1=(SELECT comp_name FROM @T1 b WHERE a.comp_name = b.comp_name)

,T2=(SELECT comp_name FROM @T2 b WHERE a.comp_name = b.comp_name)

,T3=(SELECT comp_name FROM @T3 b WHERE a.comp_name = b.comp_name)

,T4=(SELECT comp_name FROM @T4 b WHERE a.comp_name = b.comp_name)

FROM AllComputers a

ORDER BY CAST(STUFF(a.comp_name, 1, 4, '') AS INT)

Almost, kinda, sorta like a CROSSTAB query.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

It can also be done using OUTER APPLY, which could be especially handy if you need to return additional columns from each of the 4 tables.

;WITH AllComputers AS (

SELECT comp_name FROM @T1 UNION

SELECT comp_name FROM @T2 UNION

SELECT comp_name FROM @T3 UNION

SELECT comp_name FROM @T4)

SELECT a.comp_name

,T1=b.comp_name

,T2=c.comp_name

,T3=d.comp_name

,T4=e.comp_name

FROM AllComputers a

OUTER APPLY (SELECT comp_name FROM @T1 b WHERE a.comp_name = b.comp_name) b

OUTER APPLY (SELECT comp_name FROM @T2 b WHERE a.comp_name = b.comp_name) c

OUTER APPLY (SELECT comp_name FROM @T3 b WHERE a.comp_name = b.comp_name) d

OUTER APPLY (SELECT comp_name FROM @T4 b WHERE a.comp_name = b.comp_name) e

ORDER BY CAST(STUFF(a.comp_name, 1, 4, '') AS INT)

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St