July 22, 2015 at 8:00 am
Hello people, I have a little question
I currently have a query that uses three tables [Table1, Table2, Table3]. Each table only has two columns: Group and something else. I want a query that gives me the group and those three something else.
Table1: Group and Info1
Table2: Group and Info2
Table3: Group and Info3
Query: Group, Info1, Info2, Info3
The groups never repeat in a single table.
However, the groups are not identical in every table. I want the groups to never repeat twice: All the groups should appear if they are there at least once in one of the table, and provide Info1, Info2, Info3 (blank if it's not there in its table, filled if it is).
That should be a fairly simple query, I just can't seem to make it work so groups don't repeat.
Little visual example.
Table1: Group - Info1
1 - a
2 - b
4 - c
5 - d
Table2: Group - Info2
1- aa
3- bb
4- cc
Table3: Group - Info3
5- aaa
The query would give me this:
1 - a - aa - ""
2 - b - "" - ""
3 - "" - bb - ""
4 - c - cc - ""
5 - d - "" - aaa
Thank you!
July 22, 2015 at 8:36 am
Something like this?
SELECT [Group],
MAX( Info1) AS Info1,
MAX( Info2) AS Info2,
MAX( Info3) AS Info3
FROM(
SELECT [Group], Info1, '' AS Info2, '' AS Info3
FROM Table1
UNION ALL
SELECT [Group], '' AS Info1, Info2, '' AS Info3
FROM Table2
UNION ALL
SELECT [Group], '' AS Info1, '' AS Info2, Info3
FROM Table3) x
GROUP BY [Group]
You could achieve it using FULL JOINs, but that's up to you to test it.
July 22, 2015 at 8:44 am
Here's a FULL OUTER JOIN implementation. Let me know if this works:
DECLARE @Table1 AS TABLE (
GroupID int,
Info1 varchar(10)
);
INSERT INTO @Table1 (GroupID, Info1)
VALUES (1, 'a'),
(2, 'b'),
(4, 'c'),
(5, 'd');
DECLARE @Table2 AS TABLE (
GroupID int,
Info2 varchar(10)
);
INSERT INTO @Table2 (GroupID, Info2)
VALUES (1, 'aa'),
(3, 'bb'),
(4, 'cc');
DECLARE @Table3 AS TABLE (
GroupID int,
Info3 varchar(10)
);
INSERT INTO @Table3 (GroupID, Info3)
VALUES (5, 'aaa');
SELECT COALESCE(T1.GroupID, T2.GroupID, T3.GroupID) AS GroupID,
T1.Info1, T2.Info2, T3.Info3
FROM @Table1 AS T1
FULL OUTER JOIN @Table2 AS T2
ON T1.GroupID = T2.GroupID
FULL OUTER JOIN @Table3 AS T3
ON T1.GroupID = T3.GroupID
ORDER BY COALESCE(T1.GroupID, T2.GroupID, T3.GroupID);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 22, 2015 at 9:16 am
That works, thank you.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply