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