多个选择查询MySQL没有while循环

Question

I have two different tables where I need to fetch the list of store ids from one table and then find the list of coupons for those store ids.

Currently,

SELECT `storename` FROM `stores` where `brandname` = 21 

This will return something like

Store 1
Store 2
Store 3
Store 4

And I need to to run another query like

SELECT * FROM `coupons` where `storename` = {{All these stores}}

I can't use while loops because, the number of stores comes from first query can't be determined and the the output I want was not coming as expected while using while loop as I am trying to do something like

while(first query output get storename)
{

do query here

   while(second query output get all coupons per store)
   {
      // All coupons display here.
   }

}

This is making quite complicated as well, is there anyway that I can tweak my SQL query and get results easily?

Thanks

Answer 1

you can use this query:

SELECT * FROM `coupons`
where `storename` IN (
    SELECT `storename` FROM `stores` where `brandname` = 21);