SQL how to use condition on multiple column as one-CodePudding

My Table is

product_uuid                          key_name      key_value
95be9cf4-7121-492b-8725-762e6353ac51  categories    Starter
95be9cf4-7121-492b-8725-762e6353ac51  print_order   1
95be9cf4-7121-492b-8725-762e6353ac51  available     1
95be9cf4-7121-492b-8725-762e6353ac52  categories    Starter
95be9cf4-7121-492b-8725-762e6353ac52  print_order   2
95be9cf4-7121-492b-8725-762e6353ac52  available     1

Here i want to query all the product uuid which has category "Starter" and which has print_order "1" and available "1".

"?categories=Starter&print_order=1"

I Tried something like

$request = [
  "categories": "Starter",
  "print_order": "1"
]


$p = DB::table('product_properties');
foreach ($request->all() as $key => $value) {
    if($key === 'provider_uuid') {
       continue;
    }
    $p->Where([['key_name', '=', $key], ['key_value', '=', $value]]);
}
return $p->get();

But it doesnot gives the result

My Expected Result is

product_uuid
95be9cf4-7121-492b-8725-762e6353ac51

CodePudding user response：

You could join the same table three times with the different condition from each join :

select t1.product_uuid
from test_tbl t1 
inner join test_tbl t2 on t1.product_uuid=t2.product_uuid
inner join test_tbl t3 on t1.product_uuid=t3.product_uuid
where t1.key_name='categories' and t1.key_value='Starter'
and t2.key_name='print_order' and t2.key_value='1'
and t3.key_name='available' and t3.key_value='1';

Result:

product_uuid
95be9cf4-7121-492b-8725-762e6353ac51

Demo

CodePudding user response：

SELECT product_uuid
FROM tablename
WHERE (key_name, key_value) IN ( ('categories' , 'Starter'),
                                 ('print_order', '1'      ),
                                 ('available'  , '1'      ) )
GROUP BY product_uuid
HAVING COUNT(DISTINCT key_name) = 3