Avatar of quarkmike
quarkmike
Flag for France asked on

Retrieve number of products per family per category

Hi there,

I have 3 tables

Categories
c_id,
c_name,
c_order

families
f_id,
f_name,
f_order,
f_category

products
p_id,
p_name,
p_price,
p_family


I need to retrieve families who got 1 or more products for all categories and list like this order by c_order,f_order.


<cfoutput group="c_name">
#c_name#<hr>
<cfoutput group="f_name">
#f_name# (number of products for this family)
</cfoutput>
</cfoutput>


Thanks in advance.
Microsoft SQL ServerColdFusion Language

Avatar of undefined
Last Comment
awking00

8/22/2022 - Mon
awking00

Can you define the key relationships among the three tables?
quarkmike

ASKER
f_category bind to c_id
p_family bind to f_id
ASKER CERTIFIED SOLUTION
SANDY_SK

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
awking00

I think that might work but should add "having count(p_id) > 1" at the end.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
_agx_

@awkinggoo - I don't think you need it. The inner join does that already. Categories w/o matching products are automatically dropped from the results. Everything else should have at least 1 product.
quarkmike

ASKER
Thx a lot
awking00

Sorry, I misread >>need to retrieve families who got 1 or more products =<< as
need to retrieve families who got more than 1 product
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.