SQLTeam.com | Weblogs | Forums

SQL Union

Apologies for my lack of knowledge, and this is probably a very basic question

I have a query that consists of 2 identical queries unioned - eg

select name, version
from TableA

Union All

select name, version
from TableB

This obviously returns all items from TableA and TableB on seperate lines

What I want to do is somehow return the values on 1 line, so Name TableAVersion, TableBVersion

I know I can do this easily by joining both tables, but I am looking for instead for some way to keep the union and just change how the data is displayed\grouped.

The query above is just a sample, so the reason for keeping the union is because the queries get a lot more complex and I dont have the knowledge to adjust then in order to get the correct joins

Any thoughts


I'm doubting that will work ...

What you could do (using the JOIN route), and in case you were not aware of / had not considerd it, is this:

select A.name, A.version, ... lots of columns ..., B.version, ... lots of columns ...
    select name, version, ... lots of columns ...
    from TableA
    ... additional complex stuff ...
) AS A
    select name, version, ... lots of columns ...
    from TableB
    ... additional complex stuff ...
) AS B
    ON B.name = A.name
   AND B.AnythingElse = A.AnythingElse

into which you should be able to cut&paste the "guts" of your existing UNION queries


Just in case not obvious:

This JOIN code will ONLY include rows where [name] matches in both tables.

You could use a LEFT OUTER JOIN which would, successfully, show you all names from TableA and data from TableB matching on [name] or all B columns NULL otherwise, but that would NOT get you anything from TableB where B.[name] does NOT exist in TableA.

The UNION ALL route does NOT have that problem.

You could, perhaps, work around that (if you need to) using a FULL OUTER JOIN but its not entirely straightforward [handling / manipulating the columns]

If one table is guaranteed to be a subset of the other you could jsut make sure that the Parent is "A" and the Child is the target of the LEFT OUTER JOIN "B"

You method worked when I used it on a simple query, however I could not get it to work when adding in a more complex query

The complex query returns section, option and value related to a name.

The section, option and value could be the same for both names, the value could be different, the option and value could be different or the all three could be different

Where the section and/or option do not match – it shows on a separate line. Where the value does not match, same line

You can post your code, if you'd like folk here to take a look and make suggestions. The way to format it on this site is like this:


  ... your code here ...

Just realised that my previous post was not complete. I seem to be getting a cross join with my query.

What I need is -

Where section, option and value match - entries on 1 line
Where section and option match - entries on 1 line
Where section and or option dont match - seperate llines

select A.name, A.section,A.SECTION, A.[OPTION], A.[VALUE], B.SECTION, B.[OPTION], B.[VALUE]
SELECT     ENT.DBO.Application.dbid, ENT.DBO.Application.name, LookupTable_1.DisplayValue AS AppType, ENT.DBO.ConfigurationItemLatest.IsDeleted, 
                      ConfigurationItemLatest_1.IsDeleted AS Expr1, ENT.DBO.AppOption.part, CASE WHEN ENT.DBO.AppOption.section IS NULL 
                      THEN '*None*' ELSE ENT.DBO.AppOption.section END AS Section, CASE WHEN ENT.DBO.AppOption.opt IS NULL 
                      THEN '*None*' ELSE ENT.DBO.AppOption.opt END AS [Option], CASE WHEN ENT.DBO.AppOption.val IS NULL 
                      THEN '*None*' ELSE ENT.DBO.AppOption.val END AS Value
FROM         ENT.DBO.ConfigurationItemLatest AS ConfigurationItemLatest_1 INNER JOIN
                      ENT.DBO.AppOption ON ConfigurationItemLatest_1.ID = ENT.DBO.AppOption.BCID AND 
                      ConfigurationItemLatest_1.MaxVersion = ENT.DBO.AppOption.BCVersion RIGHT OUTER JOIN
                      ENT.DBO.LookupTable AS LookupTable_1 INNER JOIN
                      ENT.DBO.Application ON LookupTable_1.Value = ENT.DBO.Application.type INNER JOIN
                      ENT.DBO.ConfigurationItemLatest ON ENT.DBO.Application.BCID = ENT.DBO.ConfigurationItemLatest.ID AND 
                      ENT.DBO.Application.BCVersion = ENT.DBO.ConfigurationItemLatest.MaxVersion ON ENT.DBO.AppOption.object_dbid = ENT.DBO.Application.dbid AND 
                      ENT.DBO.AppOption.object_type = 9
WHERE     (LookupTable_1.CodeSet = 'AppTYpe') AND (ENT.DBO.ConfigurationItemLatest.IsDeleted IS NULL OR
                      ENT.DBO.ConfigurationItemLatest.IsDeleted = 0) AND (ConfigurationItemLatest_1.IsDeleted IS NULL OR
                      ConfigurationItemLatest_1.IsDeleted = 0) AND APPLICATION.NAME = 'CONFSERV'

) AS A
SELECT     PCIO.DBO.Application.dbid, PCIO.DBO.Application.name, LookupTable_1.DisplayValue AS AppType, PCIO.DBO.ConfigurationItemLatest.IsDeleted, 
                      ConfigurationItemLatest_1.IsDeleted AS Expr1, PCIO.DBO.AppOption.part, CASE WHEN PCIO.DBO.AppOption.section IS NULL 
                      THEN '*None*' ELSE PCIO.DBO.AppOption.section END AS Section, CASE WHEN PCIO.DBO.AppOption.opt IS NULL 
                      THEN '*None*' ELSE PCIO.DBO.AppOption.opt END AS [Option], CASE WHEN PCIO.DBO.AppOption.val IS NULL 
                      THEN '*None*' ELSE PCIO.DBO.AppOption.val END AS Value
FROM         PCIO.DBO.ConfigurationItemLatest AS ConfigurationItemLatest_1 INNER JOIN
                      PCIO.DBO.AppOption ON ConfigurationItemLatest_1.ID = PCIO.DBO.AppOption.BCID AND 
                      ConfigurationItemLatest_1.MaxVersion = PCIO.DBO.AppOption.BCVersion RIGHT OUTER JOIN
                      PCIO.DBO.LookupTable AS LookupTable_1 INNER JOIN
                      PCIO.DBO.Application ON LookupTable_1.Value = PCIO.DBO.Application.type INNER JOIN
                      PCIO.DBO.ConfigurationItemLatest ON PCIO.DBO.Application.BCID = PCIO.DBO.ConfigurationItemLatest.ID AND 
                      PCIO.DBO.Application.BCVersion = PCIO.DBO.ConfigurationItemLatest.MaxVersion ON PCIO.DBO.AppOption.object_dbid = PCIO.DBO.Application.dbid AND 
                      PCIO.DBO.AppOption.object_type = 9
WHERE     (LookupTable_1.CodeSet = 'AppTYpe') AND (PCIO.DBO.ConfigurationItemLatest.IsDeleted IS NULL OR
                      PCIO.DBO.ConfigurationItemLatest.IsDeleted = 0) AND (ConfigurationItemLatest_1.IsDeleted IS NULL OR
                      ConfigurationItemLatest_1.IsDeleted = 0) AND PCIO.DBO.APPLICATION.NAME = 'CONFSERV'

) AS B
ON B.name = A.name