Click here to monitor SSC
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
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5687 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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6663

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
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4241 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4241 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