CODEWITHSUNDEEP
sqljoin
purple
purple

Reputation: 1

How can I insert and join from three tables?

I have to combine two tables and replace a value within a table with values from a 3rd column.

the tables look like this order

order   date    product_type    quantity    cost 
1      1/1/19   tops             2          49.99
2      1/2/19   bottom           3          190
3      1/2/19   jewelry          1          30

product             
order   date    product type    product name    
1       1/1/19  tops            sophia tank     
2       1/2/19  bottom          boyfriend jeans 
3       1/2/19  jewelry         alex necklace   

 type               
ascin_product      ascin            
tops               0081112          
bottom             121412           
jewelry            12412415

The output should look like this

order   date    quantity    cost    product name    ascin
1      1/1/19     2        49.99    sophia tank     0001112
2      1/2/19     3        190      boyfriend jeans 121412
3      1/2/19     1        30       alex necklace   12412415

[enter image description here][2]

I can join order and product easily, I just don't know how to join the 3rd table based off of ascin product and then replace product type with ascin in the overall table 

select order, date, product_type,product_name from product
inner join on order
product.product_type=order.product type
inner join on type
product.product_type=order.product type=type=ascin_product

I don't know how to replace product type in my table with ascin

Upvotes: 0

Views: 40

Answers (2)

Joakim Danielson
Joakim Danielson

Reputation: 51945

Start from Order and then join the other tables

SELECT o.order, o.date, o.quantity, o.cost, p.product_name, t.ascin
FROM order o 
JOIN product p ON p.order = o.order
JOIN type t ON t.ascin_product = o.product_type

Upvotes: 1

Jiggy
Jiggy

Reputation: 1

I would create a view that joins product and type as a common object to select out of that is always a "complete" set of columns for product. You will probably end up using it in a variety of places and not have to join those tables over and over.

Then I would do what you have already done and just join on orders and the view to get what you are looking for.

Upvotes: 0

Related Questions