CODEWITHSUNDEEP
sqlms-access
RJIGO
RJIGO

Reputation: 1943

How to query to create new database table from 2 separate tables?

I have 2 tables.

Table 1 has Product ID and Product Name (each row has a unique Product ID).

1 Name1
2 Name2
3 Name3

Table 2 has Product ID and Color (Red, Green, Blue) and Price. There can be up to 3 rows with the same Product ID depending on what color the Product ID is associated with.

1 Red $6
1 Blue $2
2 Blue $3
3 Green $5

I want to query to create a new table with the following columns: Product ID, Product Name, RedPrice, GreenPrice and BluePrice. The 2 above samples result in:

1 Name1 $6 Null $2
2 Name2 Null Null $3
3 Name3 Null $5 Null

How do I do this query? Either SQL or Microsoft Access code is fine. Thanks.

Upvotes: 0

Views: 160

Answers (2)

Bertan
Bertan

Reputation: 27

İf you want to create a new table, the SQL might be like this:

CREATE TABLE Product
(
    ProductID int NOT NULL,
    ProductName varchar(50),
    Color varchar(25),
    Price decimal(8,3)
)

Upvotes: 0

Andriy M
Andriy M

Reputation: 77687

Assuming there can be no more than one price of each kind per product:

SELECT
  products.ProductID,
  products.ProductName,
  red  .Price AS RedPrice,
  green.Price AS GreenPrice,
  blue .Price AS BluePrice
FROM products
LEFT JOIN prices red   ON products.ProductID = red  .ProductID AND red  .Color = 'Red'
LEFT JOIN prices green ON products.ProductID = green.ProductID AND green.Color = 'Green'
LEFT JOIN prices blue  ON products.ProductID = blue .ProductID AND blue .Color = 'Blue'

Upvotes: 1

Related Questions