InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 18:23:41 GMT20http://www.sqlservercentral.com/Forums/Topic1423373-392-1.aspxIt 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.[code="sql"];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_nameFROM AllComputers aOUTER APPLY (SELECT comp_name FROM @T1 b WHERE a.comp_name = b.comp_name) bOUTER APPLY (SELECT comp_name FROM @T2 b WHERE a.comp_name = b.comp_name) cOUTER APPLY (SELECT comp_name FROM @T3 b WHERE a.comp_name = b.comp_name) dOUTER APPLY (SELECT comp_name FROM @T4 b WHERE a.comp_name = b.comp_name) eORDER BY CAST(STUFF(a.comp_name, 1, 4, '') AS INT)[/code]Tue, 26 Feb 2013 18:06:49 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1423373-392-1.aspxHmmm... 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:[code="sql"]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 @T1SELECT 'comp1' UNION ALL SELECT 'comp2' UNION ALL SELECT 'comp3'UNION ALL SELECT 'comp4' UNION ALL SELECT 'comp5' UNION ALL SELECT 'comp6'INSERT INTO @T2SELECT '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 @T3SELECT 'comp1' UNION ALL SELECT 'comp2' UNION ALL SELECT 'comp3'UNION ALL SELECT 'comp4' UNION ALL SELECT 'comp6'INSERT INTO @T4SELECT '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 aORDER BY CAST(STUFF(a.comp_name, 1, 4, '') AS INT)[/code]Almost, kinda, sorta like a CROSSTAB query.Tue, 26 Feb 2013 18:02:53 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1423373-392-1.aspxThanks 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 CodeTable1full outer joinTable2on table1.computername = table2.computernamefull outer join table3on table1.computername = table3.computernameor table2.computername = table2.computernamefull outer join table4on table1.computername = table4.computernameor table2.computername = table4.computernameor table3.computername = table4.computernamewhere table1.Name is nullor table2.name is nullor table3.name is nullor table4.name is nullTue, 26 Feb 2013 01:14:51 GMTdoodledorfhttp://www.sqlservercentral.com/Forums/Topic1423373-392-1.aspx[code="sql"]SELECT COALESCE(t1.comp_name, t2.comp_name, t3.comp_name, t4.comp_name) AS comp_name, ...FROM dbo.Tbl1 t1FULL OUTER JOIN dbo.Tbl2 t2 ON t2.comp_name = t1.comp_nameFULL OUTER JOIN dbo.Tbl3 t3 ON t3.comp_name = t1.comp_nameFULL OUTER JOIN dbo.Tbl4 t4 ON t4.comp_name = t1.comp_name[/code]Mon, 25 Feb 2013 11:00:45 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1423373-392-1.aspxyou might want to look into a FULL JOIN of all four tables.Sat, 23 Feb 2013 15:36:47 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1423373-392-1.aspxTo 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.Sat, 23 Feb 2013 14:52:31 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1423373-392-1.aspxHello 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 [u]every[/u] computer name from each table and match them like the example below.[code="plain"]Tbl1 Tbl2 Tbl3 Tbl4comp1 Null comp1 comp1comp2 comp2 comp2 comp2comp3 comp3 comp3 comp3comp4 comp4 comp4 Nullcomp5 Null Null Nullcomp6 Null comp6 comp6Null comp7 Null NullNull comp8 Null NullNull comp9 Null NullNull comp10 Null Null[/code]Jason.Sat, 23 Feb 2013 14:48:12 GMTdoodledorf