2 quick MySQL Queries

[davepelz]

Hello,

I have been learning more and more about building complex queries using joins and case statements, but i cannot find examples on the net for the specific syntax of these 2 queries im trying to execute. They both involve taking a user defined variable, such as a store id combined with a DISTINCT and a MIN function.


Basically i have a table Inventory which has the columns ProductUPC, StoreID, and Price.

So there could be the same ProductUPC linked to different stores but each store can only have one of each ProductUPC and each ProductUPC has a price.


-Both queries will have the StoreID variable passed to them:

1st Query (low price leader):

Select all the products (ProductUPCs) where the StoreID passed to the query has the lowest price of any store.


2nd Query (this store only):

Select all the products (ProductUPCs) for a given StoreID that are unique for that StoreID and not listed by any other store in Inventory table.


Would really appreciate any help,

Dan

[kovik]

What have you tried? Both of those queries sound like they'll require a GROUP BY clause.

[davepelz]

So i have been working with the group by, which i can get working for a simple query but when i try to add other parts i need i cant get it going.

So the following code works no problem:

(this is for the second query, this store only)

[code=sql]
SELECT DISTINCT
Inventory.produ ct_upc
FROM
Inventory
GROUP BY
product_upc
[/code]

But now i want to only show the distincts for a certain store so i add:
[code=sql]
SELECT DISTINCT
Inventory.produ ct_upc,
Inventory.store _id
FROM
Inventory
GROUP BY
product_upc
Having
store_id = $store_id
[/code]


In order to use having i read you have to include it in the select, but including it in the select now selects a unique combination of the product upc and store_id which produces the wrong result.

so i read this thread:
http://www.codingforums.com/showthread.php?t=84988

but so far i can't get a solution going.

[code green]

But now i want to only show the distincts for a certain store

I think you are confused about filtering records, DISTINCT and GROUP BY
If you only want to show records from a certain store then define this in the WHERE clause and not DISTINCT

[CODE=sql]
SELECT DISTINCT
Inventory.produ ct_upc
FROM tablename
WHERE Inventory.store _id = astoreID
[/CODE]

GROUP BY works like DISTINCT (I have actually found it is faster) but are you sure you don't mean ORDER BY which is very different again?

[davepelz]

Maybe im wording this poorly, but im not looking for distinct ups for one store. There is only one of each upc allowed per store, so selecting distinct upcs for a store would just return every upc.

What im trying to do is select the products that are only carried by a particular store and not by any other store in the table, which is why I am trying to do some type of filtering of the whole inventory table.

And for the low price query, im trying to show all of the products where a particular store has the lowest price on a product that multiple stores carry.

Hope that helps some,

Dan

I think you are confused about filtering records, DISTINCT and GROUP BY
If you only want to show records from a certain store then define this in the WHERE clause and not DISTINCT

[CODE=sql]
SELECT DISTINCT
Inventory.produ ct_upc
FROM tablename
WHERE Inventory.store _id = astoreID
[/CODE]

GROUP BY works like DISTINCT (I have actually found it is faster) but are you sure you don't mean ORDER BY which is very different again?

[code green]

What im trying to do is select the products that are only carried by a particular store and not by any other store in the table

Ok I am beginning to understand. It looks like you need a sub-query or TEMP table.

SELECT  store_id,product_upc 
FROM Inventory 
WHERE store_id = astoreID
AND product_upc NOT IN (SELECT product_upc FROM Inventory WHERE store_id != astoreID)

.This is rushed so not tested but it looks right. .

And for the low price query, im trying to show all of the products where a particular store has the lowest price on a product that multiple stores carry

Are you trying to find which stores have the lowest price for each product that is stocked in more than one store? This is tricky, but I will think about it

[davepelz]

Hey thanks for the help, will try testing out the subquery you suggested for the first problem.

As for second low price query, what im looking to do is have a section where you can browse products by store, and so after you select to browse a certain store, one of the views of that stores products would show which of the products they carry have the lowest price of any store.

Ideally it would only show products where at least one other store carries the product with the same upc, so it wouldn't overlap the results for the first query which showed products that are only carried by that store.

Ok I am beginning to understand. It looks like you need a sub-query or TEMP table.

SELECT  store_id,product_upc 
FROM Inventory 
WHERE store_id = astoreID
AND product_upc NOT IN (SELECT product_upc FROM Inventory WHERE store_id != astoreID)

.This is rushed so not tested but it looks right. . Are you trying to find which stores have the lowest price for each product that is stocked in more than one store? This is tricky, but I will think about it

[davepelz]

Tested out this query and works perfectly for me! Thanks a ton. One down one to go. Im thinking price one with also involve some type of subquery.

Ok I am beginning to understand. It looks like you need a sub-query or TEMP table.

SELECT  store_id,product_upc 
FROM Inventory 
WHERE store_id = astoreID
AND product_upc NOT IN (SELECT product_upc FROM Inventory WHERE store_id != astoreID)

.This is rushed so not tested but it looks right.