Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Show all data from four tables joined or matched on a particular column Expand / Collapse
Author
Message
Posted Saturday, February 23, 2013 2:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 1:44 PM
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.
Post #1423373
Posted Saturday, February 23, 2013 2:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 5,589, Visits: 24,969
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
Post #1423374
Posted Saturday, February 23, 2013 3:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 7,147, Visits: 13,213
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
Post #1423375
Posted Monday, February 25, 2013 11:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 2,037, Visits: 3,049

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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1423709
Posted Tuesday, February 26, 2013 1:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 1:44 PM
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


Post #1423917
Posted Tuesday, February 26, 2013 6:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
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!
Post #1424296
Posted Tuesday, February 26, 2013 6:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
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!
Post #1424297
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse