Sql merge multiple rows into column
I have 2 tables
Table A
A1 | A2
1 | 2
2 | 3
3 | 4
Table B
B1 | B2
1 | 3
1 | 5
4 | 3
A1,A2,B1 and B2 开发者_JAVA技巧are all ID's
I want to join Table A with Table B only when A.A1 = B.B1.
Select A.A1, A.A2, B.B2 from A JOIN B ON A.A1 = B.B1
should return
A1 | A2 | B2
1 | 2 | 3
1 | 2 | 5
But i want obtain data in this format, i would like final result as:
A1 | Col2
1 | 2
1 | 3
1 | 5
Extra question: How can i know from which column information comes?
A1 | Col2 | Table
1 | 2 | A
1 | 3 | B
1 | 5 | B
Thx for the help.
Edit1: Union wont work, i dont want to stack simply the fields from both tables, i want join data under a condition, but since A2 and B2 are ID´s of same type i would like to have data in a single collumn, and it would simplify future queries over the result.
To present multiple tables as a single table, you use UNION
:
SELECT A1 as Col1, A2 as Col2, 'A' as Col3 FROM table_A
UNION ALL
SELECT B1 as Col1, B2 as Col2, 'B' as Col3 FROM table_B
Based on the revised question, the addition of a where
condition provides the result you're looking for. I still don't see any reason that you need a join, based on the scenario presented.
SELECT * FROM
(SELECT A1, A2 as Col2, 'A' as "TABLE" FROM table_A
UNION ALL
SELECT B1, B2 as Col2, 'B' as "TABLE" FROM table_B)
WHERE A1 = 1;
Not very clear your question is, but what you want this might be it.
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
INSERT INTO a VALUES(1, 2);
INSERT INTO a VALUES(2, 3);
INSERT INTO a VALUES(3, 4);
INSERT INTO b VALUES(1, 3);
INSERT INTO b VALUES(1, 5);
INSERT INTO b VALUES(4, 3);
COMMIT;
SELECT a1 AS "1", a2 AS "2", 'A' AS "src" FROM a
UNION
SELECT a1, b2, 'B' FROM a, b WHERE a1 = b1;
1 2 src
---------------------- ---------------------- ---
1 2 A
1 3 B
1 5 B
2 3 A
3 4 A
Something like this, possibly:
SELECT
u.*
FROM (
SELECT A1, B1 AS Col2, 'A' AS SourceTable FROM A
UNION ALL
SELECT B1, B2 AS Col2, 'B' AS SourceTable FROM B
) u
INNER JOIN (
SELECT A1 FROM A
INTERSECT
SELECT B1 FROM B
) i ON u.A1 = i.A1
精彩评论