Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Unanswered: Query Logic - SQL Help

    I have some table data and know how I want the results but I'm just having a bit of trouble in constructing the SQL logic to obtain the desired results. There's a site where visitors are able to select from a list of parts, and it will return a set of model/products that they can produce with the selected parts. Here's the data ...

    Code:
    tblModel                   tblPart 
    ModelId   ModelName        PartId   PartName
    ----------------------     ----------------------
    1         Alpha            1        CHOO1 Stem
    2         Bravo            2        BH034 Rod
    3         Bravo Pro        3        HRE Seat
    
    
    tblModelPart
    ModelPartId   ModelId   PartId
    ---------------------------------
    1             1         1
    2             2         1
    3             2         3
    4             3         1
    5             3         2
    6             3         3
    ... and here's the logic that I'm trying to implement, assume that the user selects from a form, parts with the PartId 1 and 3 ...

    1. Return all models that contain only the parts selected.

    Code:
    ModelId   ModelName
    ---------------------
    2         Bravo
    2. Return all models that contain the parts selected, and may contain other parts.

    Code:
    ModelId   ModelName
    ---------------------
    2         Bravo
    3         Bravo Pro
    ... so do you have any idea on how the SQL would look for either of these queries?

    Thanks in advance,
    Goran

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What have you tried so far?

    This sounds a lot like homework to me, and at DBForums we don't do homework outright (although we'll often provide guidance).

    For what it is worth, I'd probably read up a bit on the EXISTS clause if I were you.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    HOMEWORK!!!

    yes, i have a very clear idea what the SQL would like like

    you must give it a try yourself, nobody here does homework assignments

    however, we will help you with it, once you put in the necessary effort

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rats, sniped again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    lmao, never been accused of that before, my programming skills (and age) are much beyond student level but my SQL leaves alot to be desired. The tables have been simplified and context changed to protect the nature of the site so I'm sorry if it appears too simple.

    One thing I've tried is querying the table tblModelPart for the selected parts, grouping on ModelId and if the count matches the number of components selected on the client side form then voila! you have a match. It didn't work out so well and didn't get me any further to sussing out the 2nd piece of logic.

    I've never really used the EXISTS clause (yes I know, blasphemy!) but will check it out later. Thanks for the pointer

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by GoMo
    One thing I've tried is querying the table tblModelPart for the selected parts, grouping on ModelId and if the count matches the number of components selected on the client side form then voila!
    this is the solution i would have offered, it works ~so~ well

    could you show your query please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    I think I may of had a case of things-always-seem-much-simpler-when-explaining-to-someone-else syndrome :s The query I was using has been left at home so I decided to quickly knock one up as example and it seems to work now.

    Code:
    SELECT ModelId
    FROM tblModelPart
    WHERE (PartId = 1 OR PartId = 3)
    AND (SELECT COUNT(*) FROM tblModelPart AS tblModelPart2 WHERE tblModelPart2.ModelId = tblModelPart.ModelId) = 2
    GROUP BY ModelId
    HAVING COUNT(ModelId) = 2
    Include the line in red for logic #1 and exclude it for #2. Is it really that simple or am I missing something completely obvious? I'd still be interested in seeing if there's a way using EXISTS.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since we are looking for presence or absence of more than one row, i.e. a group, therefore GROUP BY is necessary
    Code:
    SELECT ModelId
      FROM tblModelPart
    GROUP 
        BY ModelId
    HAVING ...
    this is the basic approach, which you anticipated

    now for specific situations, simply count what you want

    for #1, use
    Code:
    HAVING SUM(CASE WHEN PartId IN ( 1,3 )
                    THEN 1 ELSE 0 END) = 2
       AND SUM(CASE WHEN NOT PartId IN ( 1,3 )
                    THEN 1 ELSE 0 END) = 0
    for #2, remove the AND condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, #1 can be simplified (and made more efficient) like this:
    Code:
    SELECT ModelId
      FROM tblModelPart
     WHERE PartId IN ( 1,3 )
    GROUP 
        BY ModelId
    HAVING COUNT(*) = 2
    this means fewer rows go into the grouping process
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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