Products increasing by number of available locations

First of all: Use tables alias names consequently in your query.

Then you need either an EXISTS predicate in the WHERE clause or you need to GROUP BY  / DISTINCT your result. EXISTS() can be in many situations faster than GROUP BY / DISTINCT.

E.g. something like

SELECT   prod_name ,
         start_date ,
         end_date ,
         reg_price ,
         sale_price ,
         bbp.prod_id ,
         vouch_limits ,
         sp_name ,
         order_id ,
         img_name ,
         po.outlets ,
         po.outlet_id ,
         product_slug ,
         frontend ,
         COUNT(voucher_id) AS totalVouchers ,
         SUM(CASE WHEN purchased = '0000-00-00' THEN 1
                  ELSE 0
             END) AS TotalWithDate0000
FROM     vouchers v
         INNER JOIN bb_products bbp ON bbp.prod_id = v.product_id
         INNER JOIN service_providers sp ON sp.service_provider_id = bbp.provider_id
         INNER JOIN prod_pics pp ON pp.prod_id = bbp.prod_id
         INNER JOIN (   SELECT   product_id ,
                                 outlet_id ,
                                 COUNT(DISTINCT outlet_id) AS outlets
                        FROM     prod_outlets
                        GROUP BY product_id ) po ON po.product_id = bbp.prod_id
WHERE    pp.frontend = 'yes'
         AND pp.featured = 'yes'
         AND bbp.start_date <= :now
         AND bbp.end_date >= :now
         AND EXISTS (   SELECT *
                        FROM   prod_locations pl
                        WHERE  pl.product_id = bbp.prod_id
                               AND (   pl.location_id = :prod_location
                                       OR prod_location_id = :prod_location ))
GROUP BY bbp.prod_id;
LIMIT :limit
OFFSET :offset

Select allOpen in new window

Thank you. This is giving me the correct number now but is only showing 1 product result instead of the 3 I should see.

D'oh??

If I have 1 product with 3 locations, it shows that there are 3 products etc. This isn't what should be happening. The product total should always be 1.

You wrote, that you want only one..?

This is a search function. So, it should show multiple results as in many search results but the quantity of the individual product should have been for example, 1, not only show 1 single product in the search results list.

Sorry for not being clear!

hmm, it's not really gettin' better..

How does your model look like? What is the cardinality between products and locations - I assume bb_products and prod_locations?

When you add a product, there are 2 dropdowns. One for the primary location which is a required field. Then there are secondary locations which are optional.

So, let's say I add 1 primary location and 2 secondary locations.

If I perform 3 separate searches and in search one I choose location one, it should also should up if I search location 2 or 3.

But it is multiplying the stock count by the number of secondary locations I have.

I just checked now, I have 3 in stock and have 2 secondary locations so it is showing I have 6 on hand when I in fact only have 3.

The required location is in bb_products and the secondary ones are in prod_locations as you mentioned which stores the product id and location id

The strange thing is that this only happens when I search by primary location. If I search by secondary location it shows 3 in stock as it should. The plot thickens...

Sorry, I forgot to add that the primary location is stored in bb_products and the secondary locations in prod_locations

I asked for the model, which is the last sentence:

The required location is in bb_products and the secondary ones are in prod_locations as you mentioned which stores the product id and location id

This is not a correct normalized relational model. Location is an entity and belongs to its own table.

Thus the problem with your query. Also simplify the query to its core:

SELECT   bbp.*
FROM     bb_products bbp        
WHERE    bbp.start_date <= :now
         AND bbp.end_date >= :now
         AND ( 
            bbp.prod_location_id = :prod_location
            OR
            EXISTS (   SELECT *
                       FROM   prod_locations pl
                       WHERE  pl.product_id = bbp.prod_id
                              AND  pl.location_id = :prod_location
                                      )
            )
LIMIT :limit
OFFSET :offset

Select allOpen in new window

btw, the problem was: I couldn't identify your "primary location in the products table", because you didn't use tables alias names consequently.

Yeah, I know it isn't ideal. But the additional locations were added as an afterthought by owner of the site and I had to work around it. Having their own table would have been better. But there is so much data already stored in the bb_products table.

I will have to somehow change it which will probably mean downtime but for now I just need something that works in the interim so the stock showing is correct. If that is not possible then I will have to just bite the bullet and change it. It doesn't seem like there is another solution, well one hasn't been posted yet.

I posted that already, read the complete post.

Thanks ste5an, I am trying to implement that with my joins as your code doesn't show the other tables that I need to join.

I don't actually think that the issue is what we think it is.

I left off the bb_products table in the condition and it still happens. I changed:

AND (pl.`location_id` = :prod_location OR `prod_location_id` = :prod_location)

Select allOpen in new window

to

AND pl.`location_id` = :prod_location

Select allOpen in new window

So now it isn't even looking at the location in the bb_products table? Unless I am mistaken.

*cough*

No. The first step is to verify, that the core query is correct. Then you implement the rest.

The rectangle is the condition for the primary location in the products table. The blue the condition for the secondary locations in the location table:

I don't actually think that the issue is what we think it is.

In this case, you need to describe your data model.. cause I'm currently only guessing.

Well, I first want to establish if what I am saying is true. This:

AND (pl.`location_id` = :prod_location OR `prod_location_id` = :prod_location)

Select allOpen in new window

checks both product_locations table and bb_products table

So, I changed it to:

AND pl.`location_id` = :prod_location

Select allOpen in new window

meaning that it should ONLY check the prod_locations table

Correct?

No, step back and look at your model. You said, that the primary location is stored in the products table. Thus means - I guess:

SELECT   bbp.*
FROM     bb_products bbp
WHERE    bbp.prod_location_id = :prod_location

Select allOpen in new window

And then you have mentioned the secondary location, again I guessed:

SELECT   bbp.*
FROM     bb_products bbp
WHERE    EXISTS (   SELECT *
                    FROM   prod_locations pl
                    WHERE  pl.product_id = bbp.prod_id
                            AND  pl.location_id = :prod_location
                )

Select allOpen in new window

Thus when you search for all locations:

SELECT  bbp.*
FROM    bb_products bbp
WHERE   bbp.prod_location_id = :prod_location
    OR  EXISTS (   SELECT *
                    FROM   prod_locations pl
                    WHERE  pl.product_id = bbp.prod_id
                            AND  pl.location_id = :prod_location
                )

Select allOpen in new window

Yes, the primary location is stored in the products table but I am trying to ignore that.

So, I changed the primary location to something else and added the original primary location to the secondary location.

So, let's say for arguments sake it looked like this:

Primary Location
USA

Secondary Locations
Brazil
Mexico

I now changed it to:

Primary Location
Norway

Secondary Locations
USA
Mexcio
Brazil

So, if I searched in Norway, the product should not show up because I am now ignoring the primary location in the bb_products table. The product should only show if I selected USA, Mexico or Brazil.

Then you need to search only in the secondary locations...

SELECT   bbp.*
FROM     bb_products bbp
WHERE    EXISTS (   SELECT *
                    FROM   prod_locations pl
                    WHERE  pl.product_id = bbp.prod_id
                            AND  pl.location_id = :prod_location
                )

Select allOpen in new window

I decided that the best thing would be to remove the location from the products table and only use the locations table for locations. It is more work but is the right thing to do. Thanks, ste5an.