SQL SELECT FROM Many To Many

[chrispem]

Ok, I really need some help.

I have three tables: products, bundles, and bundleItems

product(id, name)
bundle(id, name)
bundleItems(productid, bundleid)

OK, What I want to do is return a list of ALL distinct bundles and if they have products associated or not.

for example:

bundleid bundlename hasproducts
1 get 1 free false
2 discount true
3 multibuy false

Any ideas welcome but I want the SQL to be efficient as there could be many records.

Thanks in advance.

Chris

 

[r937]

select bundle.id
     , bundle.name
     , case when exists
        ( select 1 from bundleItems
            where bundleid = bundle.id )
            then 'true' else 'false'
         end as has products
  from bundle

select bundle.id
     , bundle.name
     , case when count(bundleid) = 0
            then 'true' else 'false'
         end as has products
  from bundle
left outer
  join bundleItems
    on bundleItems.bundleid = bundle.id
group
    by bundle.id

r937.com | rudy.ca

 

[chrispem]

Many thanks, not seen the CASE used in SQL before. Will read up on it.

Have a great day.