MariaDB - search over multiple rows with same id



  • I have table with three columns in it. First is product_id linked to another table, second is att_id with link to another table and last one is val_id linked to different table.

    There are rows with same product_id with val_id and att_id combinations. Every product_id can have different number of val_id (as seen in below example). Table looks like:

    product_id att_id val_id
    77 1 7
    78 1 10
    78 1 2
    79 3 2
    79 2 1
    79 2 1
    80 3 2
    80 2 1
    80 1 1
    81 2 2
    81 1 1
    81 2 1
    82 2 1
    82 2 1
    83 2 1
    84 3 2
    85 3 2
    85 2 2
    85 2 1

    My goal is to select product_id where val_id is equal to (1 AND 2). There can be more val_ids based on user input.

    So, expected result from example above should be:

    product_id
    79
    80
    81
    85

    I cannot figure it out since every product_id, val_id combination is in different row.

    So any help will be appreciated.



  • I think you are looking for something like:

    select product_id
    from test_tbl
    where val_id in (1,2) 
    group by product_id
    having count(distinct val_id) = 2;
    

    Result:

    product_id
    79
    80
    81
    85

    https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=f2acb41820f3d371dfdcaeaf965cbce9


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2