Link to home
Create AccountLog in
Avatar of tmajor99
tmajor99

asked on

MS SQL - Merge two tables

I am trying to merge two tables with different number of records.   I just want to add the "Price" from Table B to Table A joining by Class.


Table A                                Table B

-------------                        ----------------------

Class    Attribute                 Class       Price

 A          123456                      A           9.99

 A          840333                      A           6.88

 A          320000                      B           5.98        

 B          489990                      B          4.00    


How can I marge tables to look like this?

Class    Attribute     Price

A           123456       9.99

A            840333      6.88

A           320000 

B          489990       5.98

B                               4.00


Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

What attribute goes with what price?
and will you always have more rows in tableA than you have matching in tableB?

in other words can you ever have:

Table A                                Table B
-------------                        ----------------------
Class    Attribute                 Class       Price
 A          123456                      A           9.99
 A          840333                      A           6.88
                                                A           5.98        
 B          489990                      B          4.00    
Avatar of tmajor99

ASKER

either table can have more rows than the other.
and the answer to the first question?
Price needs to be included and price is only included in table B.
For A:
What decides that 123456 get's a price of 9.99, or 6.88 or 5.98?

You need something to join the two tables that says attribute X gets price Y
one more question:
What if tableA has a class D and tableB has a class E with no matches in the other table.  What do you expect to see?

Table A                                Table B
-------------                        ----------------------
Class    Attribute                 Class       Price
 A          123456                      A           9.99
 A          840333                      A           6.88
 A          320000                      B           5.98        
 B          489990                      B          4.00    
 D          111111111                    E          1.23
Time to ask the important question: Why on earth do you want to merge it?

What's wrong with a good, old FULL JOIN?

SELECT A.*, B.*
FROM TableA A
    FULL JOIN TableB B ON A.Class = B.Class;

Open in new window


p.s. post concise and complete examples with your post instead of ASCII art. Then we can use it as template for more concrete and profound answers.
Full join won't produce the one attribute, one price or null if there isn't a 1-for-1 match.

The first art and the full join produced:
A	123456	A	9.99
A	123456	A	6.88
A	840333	A	9.99
A	840333	A	6.88
A	320000	A	9.99
A	320000	A	6.88
B	489990	B	5.98
B	489990	B	4

Open in new window


not what is in the desired results.

I prefer 'text art' as opposed to images.  It took me about 30 seconds with a good editor to convert that art into DDL and DML for the test case.
Well, the only other "merge" is a FIFO pairing using ROW_NUMBER().
That's why I'm waiting for the answers to my questions.

ROW_NUMBER likely isn't going to matter depending on the answer to my 'D' and 'E' question above.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

As no doubt you've decided, if you don't have an "id" column, you could order by a datetime or any other column that provides the order you want for matching rows.