[RESOLVED] join problem - to many rows

[laurus]

Hi,

I have 2 tables (coupons and stores), they share store_id.

I want to show ALL STORES but only once to display.

If the store has a match (one or more rows) in 'coupons' I need to know about it so I can link to that store and then see all coupons.
I must need a subquery?

this doesn't work;

select distinct s.store_name, s.store_id, s.link, s.discount_desc, s.reb_comm_perc, c.coupon_id
from stores s
left outer join coupons c on c.store_id =s.store_id
order by s.store_name

[Piranha]

select s.store_name, s.store_id, s.link, s.discount_desc, s.reb_comm_perc, c.coupon_id
from stores s
left outer join coupons c on c.store_id =s.store_id
order by s.store_name
group by s.store_name

That will work. But it will mess up the c.coupon_id, only the first will be returned. I recommend that you count how many coupon_ids there is instead, then it is easy to determine what to do:

select s.store_name, s.store_id, s.link, s.discount_desc, s.reb_comm_perc, COUNT(c.coupon_id)
from stores s
left outer join coupons c on c.store_id =s.store_id
order by s.store_name
group by s.store_name

Note that I have never managed to learn if group by or order by should be first. I usually manage to place it wrong, you just have to check that out.

[laurus]

Outstanding!

thank you very much