March 24, 2016 at 3:43 pm
Here is code that describes a simple multi table JOIN. I am new to T-SQL and wanted to confirm the logic of this code.
SELECT product_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
Why is there not a table qualifier with the last WHERE filter ie "order_num =2005;" should this be "orderitems.order_num =2005;" or "products.order_num=2005;"?
March 25, 2016 at 12:34 am
You are using an old and deprecated join method. You need to learn to use inner and left joins (among others) and aliases. It will make your code easier to read. And using joins you can easily see the answer to your 2nd question.
SELECT p.product_name, v.vend_name, p.prod_price, o.quantity
FROM orderitems o
INNER JOIN products p on p.prop_id = o.prop_id
INNER JOIN vendors v on v.vend_id = p.vend_id
WHERE o.order_num = 20005;
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 25, 2016 at 7:00 am
I agree with LinksUp. You need to use the modern syntax. That old method, while still partially supported (you can't do OUTER JOIN using the old mechanism any more), it's only partially supported, so you will run into issues.
To directly answer your question, someone got lazy in their code and left the table name off. That's functionally acceptable as long as the column names are not duplicated between tables. However, it's considered a poor coding practice. Again, follow along with what LinksUp wrote.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 25, 2016 at 7:12 am
Great. Thank you and I can't tell you how much I appreciate the help : )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply