Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    5

    Unanswered: Show only rows where there is a full match

    Let say I have this table:

    id|item|string|value

    With the following rows:

    1|1000|Press|112
    2|1001|Press|112
    3|1001|ET|27

    What query will return only the item 1001 because that is the only record which match all the values of the string column? And then if I insert a new row for item 1000 with ET and 27 it should also show.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.id
         , t.item
         , t.string
         , t.value
      FROM ( SELECT item
                  , string
               FROM daTable
             GROUP
                 BY item
                  , string
             HAVING COUNT(*) > 1 AS d
    INNER
      JOIN daTable AS t
        ON t.item   = d.item
       AND t.string = d.string
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    5
    Thank you for a swiftly reply... Though I see you are onto something I can't really put it into my project. So I'll try to explain.

    I have a product table:
    id|name

    I have a filter table
    id|product_id|filter_string|filter_value

    I have a session table
    id|session_id|filter_string|filter_value

    The two first tables are predefined by staff. For example:

    Product table:
    1|Wheel for your car
    2|Another wheel for your car
    3|Last wheel for you car

    Filter
    1|1|Size|18
    2|1|Width|12
    3|2|Size|18
    4|3|Size|19

    Then the user on our website selects his filter by adding the following row to the session-table:

    1|session1|Size|18

    This should return the products from the product-table that matches the filters with size as string and 18 as value. So far, so good... But then the user on the website adds another filter:

    2|session1|Width|12

    Now he should ONLY show the product that matches those both filters. But all my queries gives back the product ID 2 as well since it matches one of the rows in the user-inputed filter.

    Any ideas on this?

  4. #4
    Join Date
    Feb 2010
    Posts
    5
    I'm trying by the way:

    Code:
    SELECT * FROM product_table
    JOIN filter_table ON product_table.id = filter_table.product_id
    JOIN session_table ON session_table.value = filter_table.value AND session_table.string = filter_table.string
    GROUP BY product_table.id
    Works, but gives me all products within the filters.. when I add filter no 2 and so on it still gives me all

  5. #5
    Join Date
    Feb 2010
    Posts
    5
    I'm trying by the way:

    Code:
    SELECT * FROM product_table
    JOIN filter_table ON product_table.id = filter_table.product_id
    JOIN session_table ON session_table.value = filter_table.value AND session_table.string = filter_table.string
    GROUP BY product_table.id
    Works, but gives me all products within the filters.. when I add filter no 2 and so on it still gives me all

Posting Permissions

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