Joining columns from two tables into one column

[RossouwDB]

Good Day, I wonder if it is possible to joins two columns from two tables (ie column 1 from table A and column 2 from table B) into one column. Here are my tables (note that the columns from both the tables have the same names) TableA

----------------------
|Column1 | Column2 |

| aaaa | 12345 |

| bbbp | 12245 |

| ccc | 12245 |

TableB

----------------------
|Column1 | Column2 |

| asd | 12245 |

| bbb | 12245 |

| ddd | 12245 |

The first part of my SQL statement combines the two columns of each table into one column in each table, thus TableA

------------------
|Column3 |

| aaa : 12345 |

| bbbp : 12245 |

| ccc : 12245 |

TableB

------------------
|Column4 |

| asd : 12245 |

| bbb : 12245 |

| ddd : 12245 |

Now, what I want to do is to combine these two columns into one column. The SQL statement below gives me the following result Result :sigh:

------------------------------
| Column 1 | Column2 |

| bbbp : 12245 | asd : 12245 |

| bbbp : 12245 | bbb : 12245 |

| bbbp : 12245 | ddd : 12245 |

| ccc: 12245 | asd : 12245 |

| ccc: 12245 | bbb : 12245 |

| ccc: 12245 | ddd : 12245 |

What I want is this : :confused:

----------------
| Column1 |

| bbbp : 12245 |

| ccc: 12245 |

| asd : 12245 |

| bbb : 12245 |

| ddd : 12245 |

Here is my SQL statementSELECT TableA.Column1 + ' : ' + TableA.Column1 AS Column3, TableB.Column1 + ' : ' + TableB.Column2 AS Column4 FROM TableA FULL OUTER JOIN TableB ON TableA.Column1 = TableB.Column1 WHERE (TableA.Column1 IN (SELECT Column1 FROM TableA WHERE (Column2 = '12245'))) AND (TableB.Column1 IN

[David Skelly]

Well, maybe I'm missing something but I don't see how that query gives the result set that you say it does. You are joining tables together where A.Column1 = B.Column1, but in the result set that you have put here, the first row shows A.Column1 = bbbp and B.Column1 = asd. Why can't you just use a UNION to get what you want?

[Mycroft Holmes]

As David said go with a union, if there are dupes in the result then use distinct.

SELECT TableA.Column1 + ' : ' + TableA.Column1 AS Column3
FROM TableA

UNION
SELECT TableB.Column1 + ' : ' + TableB.Column1 AS Column3
FROM TableB

Never underestimate the power of human stupidity RAH

[RossouwDB]

Puurrrrrrfect! :thumbsup: Thanks a lot! (Note, column1 should have been column2), but nevertheless, it works beautifully! :-D :-D :-D Enjoy the rest of the day Rossouw

[Jorgen Andersson]

Mycroft Holmes wrote:

As David said go with a union, if there are dupes in the result then use distinct

Aren't you mixing up UNION with UNION ALL? UNION is supposed to be distinct while UNION ALL gives you all rows I thought.

"When did ignorance become a point of view" - Dilbert

[Mycroft Holmes]

Jörgen Andersson wrote:

Aren't you mixing up UNION with UNION ALL?

Uhm yes I am :-O

Never underestimate the power of human stupidity RAH

[Mycroft Holmes]

Normaly I am reluctant to supply code, I prefer to give hints only - as David did and encourage you to do some research but as you posted some code you were abviously willing to learn. Look into UNION and UNION ALL as suggested by Jorgen.

Never underestimate the power of human stupidity RAH