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
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 ...
FROM
(
select name, version, ... lots of columns ...
from TableA
... additional complex stuff ...
) AS A
JOIN
(
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
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
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]
FROM
(
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
JOIN
(
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
ORDER BY NAME, A.SECTION, A.[OPTION], B.SECTION, B.[OPTION]