Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conundrum Involving Exists Predicate in a Query (2000)

    The attached Cust_Bikes_No_Helmets_EXISTS query is as follows:

    SELECT Customers.CustomerID, Customers.CustFirstName, Customers.CustLastName
    FROM Customers
    WHERE NOT EXISTS
    (SELECT *
    FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber
    WHERE Products.ProductName LIKE "*Helmet" <font color=red>AND Orders.CustomerID = Customers.CustomerID</font color=red>)
    AND EXISTS
    (SELECT *
    FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber
    WHERE Products.ProductName LIKE "*Bike" <font color=448800>AND Orders.CustomerID = Customers.CustomerID</font color=448800>) ;

    but why does the query work even if you remove the <font color=448800>green</font color=448800> part while it doesn't if you keep the <font color=448800>green</font color=448800> part and remove the <font color=red>red</font color=red> one?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conundrum Involving Exists Predicate in a Query (2000)

    In your database, all customers bought either bikes or helmets or both, there are no customers that bought neither. So leaving out AND Orders.CustomerID = Customers.CustomerID from the second part makes no difference - customers that didn't buy helmets, bought bikes anyway. But leaving out this condition from the first part means that you exclude everyone, so no records are returned.
    If you had some customers that bought neither bikes nor helmets, they would not be returned in the query "as is", but they would if you left out AND Orders.CustomerID = Customers.CustomerID from the second part.
    Attached Images Attached Images
    • File Type: png x.png (5.0 KB, 0 views)

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conundrum Involving Exists Predicate in a Query (2000)

    Thanks Hans,
    Your reply clarifies everything and your picture shows you're well grounded on set theory.
    Ever been to Italy?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conundrum Involving Exists Predicate in a Query (2000)

    Hi Giorgio,

    I have been to Italy a couple of times, but it is a while ago now. My best friends just returned from a very nice vacation in Italy, mostly in Toscana and Lazio, but also a few days in Friuli, near Trieste.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •