SQLTeam.com | Weblogs | Forums

Joining 2 tables


#1

Hi,

I have to join 2 tables

Both the tables have records in row format

But while joining, the column values in table 1 needs to be joined with the row values in table 2

Please can you let me know how to do the above join

Usually i use the joins command to join the column values between 2 tables, but here i need to join the column value of the first table with the row value from the 2nd table

Thanks


#2

I will confess to being confused. Perhaps some example data and table schemata would clarify things.


#3

Customer Table


#4

Product tabl


#5

Output table


#6

Is it possible to get the Products table create script?


#7

The Product table should be re-designed. A Product has attributes, in this case Name, Price and Unit. The "relation" should attach these attributes to a specific instance of a Product. The table should be four columns wide and include the ID, Name, Price and Unit. If you don't make this change, how will you handle adding more products? Add more columns?

Looking at your sample data, how did you determine that Rob Ken bought a Biscuit? or Steve Mark bought a Muffin? It doesn't appear to me that the information exists to make that determination.
This appears to be a many-to-many relationship between Customers and Products since one Customer could buy many Products and one Product could be bought by many different Customers. More typical is to have a third table which defines which Customer/Product pairs occurred. Sometimes called a Junction table, though it goes by many other names, it might only include the primary keys from the two tables but could include attributes of the sale as well (e.g., Date, quantity, payment info, etc.)
HTH