How to join data from two different tables but as separate rows
-
I have the need to get all rows from a table (TableA) and detailed usage data for each row from two other tables (TableB) and (TableC).
For every matched criteria in TableB or TableC there should be a separate row in the output. The joined data will need to two columns, one which is the name of the table it was matched in and another which is the ID column from the matched table.
I am currently doing this within my apps code however it is quite slow and would be better suited to run as a single query if possible. Within my app, I am simply getting the data from TableA. I then loop through every row and fire of a new query to get the usage data.
Here is an example of the data in each table and the output that I need:
-
You've been here long enough to know that your sample data should be provided in a consumable format, for us to cut & paste into SSMS.
Where does 'Extension' 1 come from in your example? You mention you want to see the Id columns, but they do not appear in your example. Your desired output should match the sample input data.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Apologies I simplified the data a little to make the example a little easier. I do required the ID's, these are the first columns in each of the tables.
The UsageCount in the required output image should also have been 2 not 7000.
CREATE TABLE TableA
(
[Extension] INT PRIMARY KEY NOT NULL,
[Name] NVARCHAR(100) NOT NULL,
[UsageCount] INT NOT NULL
)
GO;
INSERT INTO TableA VALUES (7000, 'Example', 2)
CREATE TABLE TableB
(
[VDN] INT PRIMARY KEY NOT NULL,
[Name] NVARCHAR(100) NOT NULL,
[Var1Assignment] NVARCHAR(100) NULL
)
GO;
INSERT INTO TableB VALUES (88000, 'Example', 7000)
CREATE TABLE TableC
(
[Vector] INT PRIMARY KEY NOT NULL,
[Name] NVARCHAR(100) NOT NULL,
[StepData] NVARCHAR(MAX) NULL
)
GO;
INSERT INTO TableC VALUES (1, 'Example', 7000) -
SELECT ca1.*
FROM TableA a
CROSS APPLY (
SELECT A.Extension, A.Name, A.UsageCount, 'TableB' AS [Used By], B.VDN AS [Usage Data]
FROM TableB B
WHERE B.Name = A.Name
UNION ALL
SELECT A.Extension, A.Name, A.UsageCount, 'TableC' AS [Used By], C.Vector AS [Usage Data]
FROM TableC C
WHERE C.Name = A.Name
) AS ca1SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Thanks Scott,
Does exactly what I wanted.
-
--Removed
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Scott, would there by a way of getting this to return all rows of TableA even if there are no matches in TableB or TableC?
I tried to change it to an OUTER APPLY however all the values of the TableA are returned as NULL.
-
OUTER APPLY is the way to go, but you need to also modify the initial SELECT
SELECT ISNULL(ca1.Extension, a.Extension)
,ISNULL(ca1.Name, a.Name)
,ISNULL(ca1.UsageCount, a.UsageCount)
,ca1.[Used By]
,ca1.[Usage Data]The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks Phil, works perfect
-
Nvm, just saw the follow up comment from OP.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply