join two tables with different number of rows(in sql server)

3
votes

I have two tables: A(col1,col2,col3,col4)

B(col1,col2,col3,col4)

Table A has 4 records(rows) and B has 6 rows.I want to join them like this,for example join them in C table

C(B.col1,B.col2,A.col3,A.col4,B.col3,B.col4) (tables have different values in records just col1 and col2 contains the same values)

when i join them on A.col1=B.col1 and A.col2=B.col2 I take Cartesian product :(

P.S I want to have 6 rows in C,where B.col1,B.col2,B.col3,B.col4 have 6rows and A.col3,A.col4 have 4 rows and other 2 null

please help me..

3
sqlsql-server-2005
what d oyou mean for join? what are the foreign keys, if any? the question is not clear. – vulkanino
The join you suggest - A.col1=B.col1 and A.col2=B.col2 - should work. Post the complete SQL you're trying. – Paul Spangle
Do you want a) all rows in B and any rows in A where the keys match or b) all rows from A and B that have a unique set of key values? – Murph
Thank you very much for your participation. – kupa

3 Answers

2
votes

You need to use a FULL OUTER JOIN

Select a.Col1,
       a.Col2,
       a.Col3,
       a.Col4,
       b.Col3,
       b.Col4

From TableA a
Full Outer Join TableB b on a.Col1 = b.Col1
                         And a.Col2 = b.Col2

EDIT:

"doesn't work" is not going to help anyone. Maybe you could provide further details as to why it isn't working for you.

You could add some example data and the actual expected output to your question.

e.g

Create Table #TableA
(
Col1 
...
)

Insert Into #TableA
Values (...)

The clearer your question then the better the answers will be. If people don't fully understand what your exact problem is and expected output you want then how are we supposed to provide you with a full and correct answer.

1
votes

Try

SELECT A.col1, A.col2, A.col3, A.col4, B.col3, B.col4
FROM A
FULL OUTER JOIN B on (A.col1 = B.col1 AND A.col2 = B.col2)
1
votes

You can use left outer join there.. But I have an additional question on how to populate the empty rows with some other values than NULL..