Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Unanswered: sql search from 2 table using checkboxes

    I have a form with checkboxes and I need to make a sql to collect all products (prodID) from table product who have same (OccID) as in table OccDetail.

    First I use table Occasion to create the checkboxes with the OccName where each checkbox has it OccID. I click each checkbox and as I click it is saved in session and search for products who has this OccID and display them on the OnClick event.

    1. Table
    Occasion
    ----------
    OccID
    OccName

    2. Table
    Product
    ----------
    ProID
    ProName

    3. Table
    OccDetail
    ----------
    OcDID
    OccID
    ProID

    I need to know how I build the sql string to find all ProID in Product who has related ProID in OccDetail with the selected OccID.

    Here is the sql I am using but its not working when more then 1 checkbox is selected :

    SELECT Product.ProID, Product.ProName
    FROM (Product INNER JOIN
    OccDetail ON OccDetail.ProID = Product.ProID)
    WHERE (OccDetail.OccID = 8) AND (OccDetail.OccID = 6)

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your where clause contradicts your join. Try OR instead of AND and see if that does what you want.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2009
    Posts
    3
    thank you for your reply georgev
    the reason I am using AND and not OR is I want only products that has both 6 and 8 in OccDetail. I dont want all products that has either 6 or 8
    Maybe I didnt explain this clear enough sorry

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, then can you explain what about thhe query is not working when more than 1 checkbox is selected. Can we see the SQL that does work (i.e. only one selected) as well please.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2009
    Posts
    3
    If I select only 1 checkbox I should get all products that has that OccID even if they have more OccID
    and when I select more checkboxes I want to narrow the search and display products that has those OccID even if they have more OccID but restrict to have at least all OccID thats selected.

    This works only for 1
    SELECT Product.ProID, Product.ProName
    FROM (Product INNER JOIN
    OccDetail ON OccDetail.ProID = Product.ProID)
    WHERE (OccDetail.OccID = 8)

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm still not 100% what you want to show, but maybe this will give you the result.
    Code:
    SELECT Product.ProID
         , Product.ProName
    FROM   Product
     INNER
      JOIN (
            SELECT ProId
            FROM   occDetail
            WHERE  OccId IN (8, 12)
           ) As [hmm]
        ON Product.ProID = hmm.ProID
    George
    Home | Blog

Posting Permissions

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