July 10, 2006 at 8:36 pm
CART
_________________
CartIDProductID
115
217
315
324
389
KITS
_______________________________________________
Kitid(PK)ParentProductIDChildProductID
1 15 56
2 15 58
3 15 59
4 17 56
PRODUCTS
__________________________
ProductIDProductName
15Insurance Kit
16Television Kit
17Paper Clips
24Towels
56Life Insurance
58Car Insurance
59Home Insurance
89Telephone Directory
My query is regarding the above 3 tables.
The objective is that given a cartID, I should be able to find the name of the parent product and the name of all the childproducts associated with that parentproduct.
For example if the cartid is 1
Than my answer should be
Insurace Kit (this is the parent Product)
Life Insurance (this is the child Product)
Car Insurance (this is the Child Product)
Home Insurance (this is the child Product)
Thanks for the help
July 11, 2006 at 1:29 am
Try this:
select p1.ProductName as ParentProduct, p2.ProductName as ChildProduct
from Products p1 join Kits k on k.ParentProductId = p1.ProductId
join Products p2 on p2.ProductId = k.ChildProductId
join Cart c on c.ProductId = p1.ProductId
where c.CartId = 1
Hope that helps,
July 12, 2006 at 8:24 am
Another way that to my eyes is simpler looking:
SELECT p1.productName as ParentProduct, p2.productName as ChildProduct
FROM products p1, products p2, kits k, cart c
WHERE p1.productId = c.productId
AND k.parentProductId = p1.productId
AND k.childProductId = p2.productId
AND c.cartId = 1
The Estimated Execution Plan shows them having the same cost.
July 12, 2006 at 11:47 am
As a best practice, I would recommend staying away from "old style" joins (table names separated by commas). In certain scenarios, they can produce ambiguous results.
Stick with the "new style" joins (JOIN.. ON..) to avoid this issue.
- Ward Pond
blogs.technet.com/wardpond
July 12, 2006 at 12:00 pm
I wasn't aware of that. I join tables both ways, sometimes using both ways within the same statement. Can you give us an example that produces ambiguous results?
July 12, 2006 at 12:11 pm
Hi..
I'm away from my office (and my archives) at the moment, so it will be next week before I can get this to you.
If you look through BOL, you'll find all sorts of advice like this (from ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb9696a6-1b2f-4d0e-baa4-e9c54211ea2b.htm):
Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins.
I'll be back in touch next week when I can find better details.
- Ward Pond
blogs.technet.com/wardpond
July 12, 2006 at 12:14 pm
Here's an article from an article I found:
SELECT Customers.CustomerId
FROM customers, orders
WHERE customers.Customerid *= orders.CustomerId
AND orders.CustomerId IS NULL
ORDER BY Orders.CustomerIdI need to see a list of customers who don't have any orders, but this query returns all 91 customers, even though most of them have a record in the Orders table. How can I get the correct result set?
SQL Server lets you write an outer join two different ways. You can use the old syntax, which is proprietary to SQL Server, or you can use the new ANSI-compliant syntax for expressing the join. The old syntax places the JOIN expression in the WHERE clause and uses an asterisk (*) to indicate which table is the preserved table in the outer-join relationship. The new syntax places the JOIN expression in the query's FROM clause and explicitly uses the OUTER keyword, as the following query shows:
SELECT Customers.CustomerId
FROM customers LEFT OUTER JOIN orders
ON customers.CustomerId = orders.CustomerId
WHERE orders.CustomerId IS NULLYou're probably familiar with how syntax varies depending on whether the JOIN expression is in the WHERE clause or the FROM clause. In most cases, each query will produce an identical, and correct, result set. However, you might not be aware of problems that can occur when you combine outer joins with an IS NULL check in the WHERE clause.
Let's examine the first query to understand why SQL Server returns an incorrect result set. Your goal in this query is to join the Customers table to the Orders table, then retrieve all records where the value for CustomerId in the Orders table is NULL. However, instead of getting the correct result of two rows, you get 91. You get the incorrect result because the OUTER JOIN condition is in the WHERE clause instead of the FROM clause, causing SQL Server to perform the IS NULL check against orders.CustomerId before the join. To get the answer you want, you need to test for IS NULL after the join. But if you use the old join syntax, SQL Server evaluates the test before it processes the join. Because no rows in the Orders table have a NULL value for CustomerId, SQL Server eliminates all the Orders table's rows before it processes the outer join. So when SQL Server processes the join, it doesn't see any rows in the Orders table and, thus, incorrectly returns all customers as having no orders.
The ANSI syntax for expressing a join removes the ambiguity about when the IS NULL check should happen. When you use the ANSI syntax, SQL Server evaluates JOIN expressions before WHERE conditions in all cases. I encourage you to always place JOIN conditions in the FROM clause instead of in the WHERE clause, especially when using outer joins, so that you don't have this kind of evaluation-order problem, which can lead to wrong answers.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply