[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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

0
Jules Webb
Asked:
Jules Webb
  • 4
  • 4
2 Solutions
 
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 WebbAuthor 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 WebbAuthor 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
 
Jules WebbAuthor Commented:
Thanks, that did it:)

Jules
0
 
Jules WebbAuthor Commented:
Thanks John!!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now