March 29, 2016 at 7:22 am
Hello Forum,
I have a quick concept question. Here is the code:
SELECT prod_name, vend_name, prod_price, quanity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id=products.prod_id
AND order_num =20005;
My question: I understand that we are linking the order.product_id to the products.prod_id. I also understand that we are relating the product.vend_id to the vendors.vend_id. My question is, since we are using the "products" table in both equi-joins, that how the 4 columns are a linked together?
March 29, 2016 at 7:46 am
Well SQL Server will link the tables together based on the joins you specified. Just making some hopefully not crazy assumptions about your table designs, you would get 1 row for each line in the order items tables that matches the order number, SQL server would join that to the products table so each order item would match 1 product, and then each product would be matched to the vendor.
March 29, 2016 at 8:18 am
I don't recommend using 'comma' joins as this is not the standard and easier to make a mistake. Learn and use JOIN...trust me you'll be better off for it.
March 29, 2016 at 8:21 am
Great thank you ..helps a ton for me
March 29, 2016 at 8:22 am
Fixed query
SELECT prod_name,
vend_name,
prod_price,
quanity
FROM orderitems
INNER JOIN products ON orderitems.prod_id = products.prod_id
INNER JOIN vendors ON products.vend_id = vendors.vend_id
WHERE order_num = 20005;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2016 at 8:29 am
ok thank you helps a ton
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply