Multiple Select Query MySQL without while loop

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

Iłya Bursov · Accepted Answer · 2014-08-27 01:28:09Z

2

you can use this query:

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

answered Aug 27, 2014 at 1:28

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bienthuy · Accepted Answer · 2014-08-27 01:35:50Z

1

$query = 'SELECT t.storename, h.couponid AS couponsid, h.coupon AS couponvalue'
    . ' FROM #__stores AS t'
    . ' LEFT JOIN #__coupons AS h ON h.storename = t.storename'
    . ' where `brandname` = 21'
    . ' ORDER BY anything you like'
    ;

answered Aug 27, 2014 at 1:35

bienthuy

132 bronze badges

Collectives™ on Stack Overflow

Multiple Select Query MySQL without while loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related