sql - Join two tables based on two columns

Question

Hi my SQL is a bit rusty and need a bit help with getting a statement correct. I have the following setup table A and B:

  A        B
=====    ======
  A       A  B
  B       C  B
          A  C
          B  D
          D  A

I would like to JOIN the single column on A with both columns on B to end up with table C:

  C
=====
  A
  A
  B
  B
  B
  A

I have tried different joins, but when I use the OR operator I get way to many rows. My Setup is a bit more advanced, I hope the simplified tables above is enough to illustrate my issue. My setup is a bit more advanced in the above example. In my real world application I have two tables where I have to find all the multiple phone numbers, which can be in two columns, PHONE1 and PHONE3. First I make a intersection so I get all the phone numbers, which are represented more than once. My problem is I need to end up with a statement that return all the multiplets, and some extra data from the other columns. Here is my statement:

SELECT * FROM
(SELECT COMPANY, CONTACT, PHONE1, PHONE3, U_EMAIL, UMEDLEM, UKONKAT, UAAFMELD
FROM CONTACT1 JOIN CONTACT2 on CONTACT1.ACCOUNTNO = CONTACT2.ACCOUNTNO) as t1
INNER JOIN
(SELECT PHONE1 as PHONE FROM CONTACT1 WHERE LEN(PHONE1) > 0
INTERSECT
SELECT PHONE3 FROM CONTACT1 WHERE LEN(PHONE1) > 0) as t2 ON t1.PHONE1 = t2.PHONE

Answer 1

this is how you could join A's column on Both columns of B.

SELECT * FROM A
INNER JOIN B B1 ON A.Column1 = B1.Column1 
INNER JOIN B B2 ON A.Column1 = B2.Column2 

Or you can use UNION, to combine two query results

SELECT * FROM A
INNER JOIN B  ON A.Column1 = B.Column1 
UNION
SELECT * FROM A
INNER JOIN B  ON A.Column1 = B.Column2 

you can also consider LEFT joins, depending on how you want to handle NULL values in B.