#1
  1. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052

    wrong result from subquery in WHERE EXISTS


    Hi

    I have the following tables,

    1)category
    deptID

    2)categoryTree
    ancestor
    descendant

    3)products
    productName
    deptID

    say a category tree as (parenthesis have the respective deptID) the final category
    of each tree is called LEAF(to understand better)

    stock(1)->Hardware(24)->Bosch(37)
    stock(1)->Hardware(24)->Motorolla(40)
    stock(1)->Hardware(24)->CAT(43)->Light D(52)
    stock(1)->Hardware(24)->CAT(43)->Heavy D(53)


    now if somebody click on "Heavy D", I want to show only the products
    under this category. (products are stored in database with respective leaf ids)

    but if he clicks on the "CAT" then I want to show all products under "Light D" and "Heavy D"
    and if clicked on "Hardware" then I want to show products under all its children.


    I have two queries with which I can find ANCESTORS and DESCENDAT of any given deptID,
    ignoring itself and also ignoring root which is STOCK,because stock is parent of all categories so i want to avoid that.

    furthermore, I can used a UNION ALL to get all ANCESTORS + DESCENDANT of any given deptID.
    Code:
    SELECT  deptID,deptName FROM departments d
    JOIN deptTree dt 
    ON (d.deptID=dt.descendant)
    WHERE dt.ancestor = 24 AND dt.ancestor != dt.descendant
    
    UNION ALL SELECT deptID,deptName FROM departments d
    JOIN deptTree dt 
    ON (d.deptID=dt.ancestor)
    WHERE dt.descendant = 24 AND dt.ancestor > 1
    so far so good, now the issue is that I get all products from all categories if I want to join these
    two queries as a SUBQUERY as follow,

    Code:
    SELECT productName FROM products WHERE EXISTS(SELECT  deptID,deptName FROM departments d
    JOIN deptTree dt 
    ON (d.deptID=dt.descendant)
    WHERE dt.ancestor = 24 AND dt.ancestor != dt.descendant
    
    UNION ALL SELECT deptID,deptName FROM departments d
    JOIN deptTree dt 
    ON (d.deptID=dt.ancestor)
    WHERE dt.descendant = 24 AND dt.ancestor > 1)
    whereas, I expect it to function like I explained above. because the subquery is giving correct deptID.

    Any idea, where am I doing wrong?
    Last edited by jojoba; September 6th, 2016 at 04:21 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,658
    Rep Power
    4288
    what you're doing is the same as
    Code:
    SELECT productName FROM products WHERE 1=1
    your WHERE EXISTS will return true (same as 1=1) whenever the UNION subquery returns ~any~ rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052
    O.K... I thought the Subquery is giving all results to Query that compares one by one all of them. Does WHERE EXISTS() / IN not mean that check the
    Query/Outer against all results returned from its results? what is the correct approach to do it?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,658
    Rep Power
    4288
    WHERE EXISTS simply checks whether the subquery returns any rows

    (it actually stops executing as soon as it finds the first row, and returns TRUE)

    i'm not sure what your correct approach should be, because i don't really understand how products are related to departments, or what you're trying to achieve

    perhaps you want to join the products table to the union subquery?

    Comments on this post

    • jojoba agrees : why does it show 0 in my rep power :-s. anyway, thanks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052
    Thank you very much teacher!

    in fact, i found that the idea of UNION ALL was also wrong. I wanted to start from the given node and find all the products only for all the leafs
    under it as well. However, UNION ALL was traversing upwards i.e for given node's parents too. which was wrong for the feature i needed.

    so my final working query is as follow, posting for anybody who might find it useful in the same situation,

    Code:
    SELECT productName FROM products p, (SELECT  deptID FROM departments d
    JOIN deptTree dt 
    ON (d.deptID=dt.descendant)
    WHERE dt.ancestor = 24 
    
    ) dp WHERE  p.deptID=dp.deptID
    *24 is given / selected department/category ID

IMN logo majestic logo threadwatch logo seochat tools logo