Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: Finding a selection of products that are not prerequisite products of another product

    Product
    --------
    ProductId
    ProductName


    ProductPrequisiteProduct
    ------------------------
    ProductId
    PrerequisiteProductId

    (there is a unique non-clustered index on ProductId/PrerequisiteProductId)


    I want to pass a ProductId = 1 and query what products aren't already prerequisites of the given product. I want to see if this is correct:

    Code:
    select 
    	p.ProductId
    	, p.ProductName
    from 
    	Product p
    left join 
    	ProductPrerequisiteProduct ppp
    		on ppp.PrerequisiteProductId = p.ProductId
    where
    	ppp.ProductId <> 1
    	or
    	ppp.ProductId is null

  2. #2
    Join Date
    Mar 2003
    Posts
    130
    I just found a bug in that code.
    If a product other than ProductId = 1 has a perquisite product that Product (1) also has, it will still be shown since <> 1 won't restrict it.

    How else can this be done?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try NOT EXISTS.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Mar 2003
    Posts
    130
    I think this will work better:
    Code:
    select
    	p.ProductId, 
    	p.ProductName
    from
    	Product p
    	
    	left join	
    	(
    		select
    			ProductId,
    			PrerequisiteProductId
    		from
    			Product_PrerequisiteProduct ppp
    		where
    			ppp.ProductId = 1
    	) p2
    	
    	on p2.PrerequisiteProductId = p.ProductId
    	
    where
    	p2.ProductId is null
    	and
    	p.ProductId <> 1
    Using a CTE I can get the same result and same execution plan.

Posting Permissions

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