SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
munta
munta
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 37
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.


Tbl1 Tbl2 Tbl3 Tbl4
comp1 Null comp1 comp1
comp2 comp2 comp2 comp2
comp3 comp3 comp3 comp3
comp4 comp4 comp4 Null
comp5 Null Null Null
comp6 Null comp6 comp6
Null comp7 Null Null
Null comp8 Null Null
Null comp9 Null Null
Null comp10 Null Null


Jason.
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15861 Visits: 25280
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

Before posting a performance problem please read
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23867 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19753 Visits: 7410

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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
munta
munta
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 37
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.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17937 Visits: 6431
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'

--Tbl1 Tbl2 Tbl3 Tbl4
--comp1 Null comp1 comp1
--comp2 comp2 comp2 comp2
--comp3 comp3 comp3 comp3
--comp4 comp4 comp4 Null
--comp5 Null Null Null
--comp6 Null comp6 comp6
--Null comp7 Null Null
--Null comp8 Null Null
--Null comp9 Null Null
--Null comp10 Null Null

;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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17937 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search