Verify SQL multiple inner joins

I created a search query on my site a while back that has been working quite well  it looks like this:
SELECT products.idProduct, products.sku, products.description, products.price, products.listhidden, products.listprice, products.serviceSpec, products.bToBPrice, products.smallImageUrl,products.noprices,products.stock, products.noStock,products.pcprod_HideBTOPrice, (0) as POrder ,products.FormQuantity,products.pcProd_BackOrder FROM products WHERE (((description LIKE '%wal%') OR (SKU LIKE 'wal%'))) AND (active=-1 AND removed=0 AND configOnly = 0 AND listprice > 0 AND description <> '') ORDER BY sku ASC, idproduct DESC

Open in new window


We discovered the results were including products that were in hidden categories.  The structure of the database is like this:  idProduct is the key for the products table, and idCategory is the key for the categories table.  categories_products is the table that connects the product table to the category table, since products can be assigned to multiple tables.

Since all the configuration for a category is in the categories table, I need to link products to the categories_product table and then to the categories table to determine if the category should be visible or not. (categories.ibtoHide = 0 is the value i need to check for)

I came up with this:
SELECT products.idProduct, products.sku, products.description, products.price, products.listhidden, products.listprice, products.serviceSpec, products.bToBPrice, products.smallImageUrl,products.noprices,products.stock, products.noStock,products.pcprod_HideBTOPrice, (0) as POrder ,products.FormQuantity,products.pcProd_BackOrder FROM products inner join categories_products on products.idProduct = categories_products.idProduct inner join categories on categories_products.idCategory = categories.idCategory WHERE categories.ibtoHide = 0 and (((description LIKE '%wal%') OR (SKU LIKE 'wal%'))) AND (active=-1 AND removed=0 AND configOnly = 0 AND listprice > 0 AND description <> '') ORDER BY sku ASC, products.idproduct DESC

Open in new window


This works well, but it duplicates everything.  Adding "distinct" solves that, but I'm a little concerned that this isn't the most efficient way to write the query.  Is there a way to do it without getting duplicates?

Thanks for your help!!!
LVL 1
slightlyoffAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
You had good instincts on this: DISTINCT is not the way to go.

Since you're joining only to verify a match, as all the columns you list are from the Products table, you should use EXISTS() instead of a JOIN.  This prevents extra duplicate rows from being generated in the first place, so DISTINCT will not be needed.

Something like this:


SELECT
    p.idProduct, p.sku, p.description, p.price, p.listhidden,
    p.listprice, p.serviceSpec, p.bToBPrice, p.smallImageUrl, p.noprices,
    p.stock, p.noStock, p.pcprod_HideBTOPrice, (0) as POrder , p.FormQuantity,
    p.pcProd_BackOrder
FROM products p
WHERE
    ((p.description LIKE '%wal%') OR (p.SKU LIKE 'wal%')) AND
    (p.active=-1 AND p.removed=0 AND p.configOnly = 0 AND p.listprice > 0 AND p.description > '') AND
    EXISTS (
        SELECT 1
        FROM categories_products cp
        INNER JOIN categories c ON
            cp.idCategory = c.idCategory
        WHERE
            cp.idProduct = p.idProduct AND
            c.ibtoHide = 0
    )
ORDER BY
    sku ASC, idproduct DESC
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Please format your code as it makes it easier to read.  I also aliased the tables

SELECT p.idProduct, p.sku, p.description, p.price, p.listhidden, p.listprice, p.serviceSpec, p.bToBPrice, p.smallImageUrl,p.noprices,p.stock, p.noStock,p.pcprod_HideBTOPrice, (0) as POrder ,p.FormQuantity,p.pcProd_BackOrder
FROM products p
inner join categories_products cp on p.idProduct = cp.idProduct 
inner join categories c on cp.idCategory = c.idCategory 
WHERE c.ibtoHide = 0 and (((description LIKE '%wal%') OR (SKU LIKE 'wal%'))) AND (active=-1 AND removed=0 AND configOnly = 0 AND listprice > 0 AND description <> '') 
ORDER BY sku ASC, products.idproduct DESC

Open in new window


If the products can be in more than 1 category distinct is the only way to get around it.
0
 
Shaun KlineLead Software EngineerCommented:
You can avoid the joins by adding a condition to your WHERE clause that eliminates products that are included in categories that are hidden.

The added condition would be:
WHERE NOT EXISTS (SELECT 1 FROM categories_products inner join categories on categories_products.idCategory = categories.idCategory WHERE categories.ibtoHide = 0 and products.idProduct = categories_products.idProduct)
AND ...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
slightlyoffAuthor Commented:
Thank you very much for the feedback!  I'm glad i asked :)
I'll be back in the office tomorrow to verify and test these solutions, but i wanted to post and let you know i appreciate the help.
0
 
slightlyoffAuthor Commented:
Also, thanks Kyle, I should have considered how the SQL looked first - and I will make sure to do that in the future.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Yeah . . . the exists is the way to go . . . can't believe I missed that one.  A bit scatter brained today.
0
 
slightlyoffAuthor Commented:
Thanks so much for your help.   I've learned SQL as I've gone along - learning SELECT, UPDATE, DELETE, inner join, etc.  It isn't until I come across stuff like this that I realize I could do a lot more.  I didn't even know an EXISTS statement existed :)  - now I do.

Thank you again!
0
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.

All Courses

From novice to tech pro — start learning today.