Need help forming sql query from 3 tables

Hello

I need some help writing an sql query.  The query below works but it's not returning the results I'm expecting.

Goal: I need the category_id and title of all categories with the parent_id of 92, and whose products have a quantity of more than 0.

I added exp_br_product.quantity to the query to see why it was returning results with products whose quantity was 0 and found that the results do not accurately reflect the quantity in the exp_br_product table. The other issue is that the results are repeating 4 times.

These are the results I'm expecting
96      DEPOSIT: Fill Your Freezer      
97      DEPOSIT: Family Packs      
98      DEPOSIT: Ground Beef

I've embedded images so you could see what I'm looking at.
 Table: exp-br-category Table: exp-br-product Table: exp-br-product-category Query Results
SELECT DISTINCT exp_br_category.category_id, exp_br_category.title
FROM exp_br_product, exp_br_category, exp_br_product_category
WHERE exp_br_product.quantity > 0 AND exp_br_category.parent_id = 92

Open in new window

LVL 2
Jules WebbFreelance Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
Without looking at the actual table screenshots it is clear that you lack knowledge of SQL joins.  Suggest you study them at http://mysqljoin.com/.
0
johanntagleCommented:
Clicked "submit" too early.  Just want to say I gave this as my answer vs giving you the solution because you will be more able to handle such challenges more easily in the future if you have the proper SQL foundations.  I'm sure you will solve this yourself once you undertand SQL joins.
0
Jules WebbFreelance Web DeveloperAuthor Commented:
@johanntagle

It is true that I'm an SQL noob, and your link did a good job of explaining joins - better than the other sites I looked at, but I stand by my comma operator join
http://mysqljoin.com/joins/inner-join-comma-operator/

After reading the link above I updated my WHERE statement. I also selected a few more columns so I could get a better picture of what the results were bringing in. My results are now correct, but I only want each category title to show once. Can you give me any leads/hints/help on that?

cheers!
jules

   New Query Results
SELECT DISTINCT exp_br_category.category_id, exp_br_category.title, exp_br_product.quantity, exp_br_product.product_id
FROM exp_br_product, exp_br_product_category, exp_br_category 
WHERE ((exp_br_product.product_id = exp_br_product_category.product_id AND exp_br_product.quantity > 0) AND (exp_br_category.category_id = exp_br_product_category.category_id AND parent_id = 92))

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johanntagleCommented:
Hi Jules,

I have no problems with comma operator joins, it's what I first learned too, and for the longest time resisted ANSI standard joins.  But I learned to appreciate the latter when I started writing more complex queries so that the join columns are defined within the join statement instead on a separate where clause.  Also, as far as I know, except for Oracle-specific (+) operator for outer joins, only inner joins support comma operators for joins.  My main point in my previous post is you were not specifying the join columns at all in your previous SQLs.

Now to show the category title only once - how do you want to handle multiple products in the same category?  Are you envisioning something like:

69   DEPOSIT: Fill Your Freezer  1     2549
                                          1     2550
                                          3     2551
97   DEPOSIT: Family Packs      1     2554
....

Unfortunately I don't know of any output formatting functions within MySQL.  I usually process things like that outside MySQL.  The way I would do it in this case is to make sure I have a ORDER BY CATEGORY_ID, then in the external script check if the current row's category_id is the same as the previous one, and if so suppress printing the title.
0
Jules WebbFreelance Web DeveloperAuthor Commented:
Hi john

Thanks for the explanation, there is so much more I would like to learn, and I appreciate your input. I've bookmarked the link you sent.

------------------

Short Story
this is a category menu. the only columns I'll actually be grabbing are the catetgory_ID and title.

< li >< a href="/market-place/grass-fed-beef/category/{category_id}">{title}< /a >< /li >


Long Story
The business (grass fed beef) takes deposits every year prior to slaughter. They opted to create mirror "deposit" products in "deposit" categories to more easily track deposits sales.  So twice a year this menu will switch (depending on inventory) from deposit categories to regular categories and back again

thanks fore the assist
jules

0
johanntagleCommented:
No problem.  If you only need to grab and show the category id and title, then only do a "SELECT DISTINCT exp_br_category.category_id, exp_br_category.title" and leave out the other columns.  That will eliminate the duplicates.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jules WebbFreelance Web DeveloperAuthor Commented:
Thanks, that did it:)

Jules
0
Jules WebbFreelance Web DeveloperAuthor Commented:
Thanks John!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.