jmoriarty
asked on
Using condition logic with MySQL SQL statement syntax?
Evening!
I have a question regarding the usage of condition logic in SQL statements.
Here is the scenario.
I have a table, mmproductcategory that records are inserted into/deleted from in order to control what items exist in what category of our product catalog.
table structure is:
MMPRODUCTCATEGORY
--------------------------
PKMMCategoryID (bigint, 20)
FKCategoryID (bigint, 20)
FKProductID (bigint, 20)
Our code reads the highest primary key entry in the mmproductcategory table in order to determine the display order of products in the catalog. I.E., the newest records/items added to any category are always displayed first.
I now need to do a blanket update of all products $9.99 and less and move them into the sales category.
That part's not a problem, the query for that is fairly simple. (see attached to get a better idea of what I'm working with) However, here's the issue.
If I have products in 2 categories, such as portable_electronics, AND the sale category, and I do a blanket update, it's going to insert a second record for sale, and thus existing products in the sales category will be pushed to the front of the display order, even though they're not new items.
My question: is there a way to, either with PHP or MySQL, check if a record already exists before performing the insert? For example, Product A has an FKProductID of 55, and the FKCategoryID of Sale is 64. This item exists in portable_electronics but also already exists in sale. Can I, before performing the price-based move to the Sale category, somehow check if that FKProductID AND FKCategoryID already exists as a record in mmproductcategory?
I'm guessing there's a probably a way via script or stored procedure, but I've not worked with those types of functions much yet.
Thanks!
I have a question regarding the usage of condition logic in SQL statements.
Here is the scenario.
I have a table, mmproductcategory that records are inserted into/deleted from in order to control what items exist in what category of our product catalog.
table structure is:
MMPRODUCTCATEGORY
--------------------------
PKMMCategoryID (bigint, 20)
FKCategoryID (bigint, 20)
FKProductID (bigint, 20)
Our code reads the highest primary key entry in the mmproductcategory table in order to determine the display order of products in the catalog. I.E., the newest records/items added to any category are always displayed first.
I now need to do a blanket update of all products $9.99 and less and move them into the sales category.
That part's not a problem, the query for that is fairly simple. (see attached to get a better idea of what I'm working with) However, here's the issue.
If I have products in 2 categories, such as portable_electronics, AND the sale category, and I do a blanket update, it's going to insert a second record for sale, and thus existing products in the sales category will be pushed to the front of the display order, even though they're not new items.
My question: is there a way to, either with PHP or MySQL, check if a record already exists before performing the insert? For example, Product A has an FKProductID of 55, and the FKCategoryID of Sale is 64. This item exists in portable_electronics but also already exists in sale. Can I, before performing the price-based move to the Sale category, somehow check if that FKProductID AND FKCategoryID already exists as a record in mmproductcategory?
I'm guessing there's a probably a way via script or stored procedure, but I've not worked with those types of functions much yet.
Thanks!
//Query to insert all products into the sales category where product //value = $9.99 or less
insert into mmproductcategory(Fkproductid,fkcategoryid) (select distinct(mmproductgroupdetail.FKProductid),<CATEGORY ID OF SALE> from mmproductgroupdetail,MMPRODUCTCATEGORY where mmproductgroupdetail.price <=9.99)
ASKER
Yeah, I was trying to think of a way to check the PK as well, but there's no reference point for it outside mmproductcategory.. For example, you could have 2 entries for the same item under the sales category, and it'd show up twice in the admin panel, etc.
I think I understand what you're talking about with the second paragraph, I hadn't considered using an inner/outer join setup. If you have the time, I'd gladly take you up on the offer for example code.
Thanks!
-James
I think I understand what you're talking about with the second paragraph, I hadn't considered using an inner/outer join setup. If you have the time, I'd gladly take you up on the offer for example code.
Thanks!
-James
Here are the statements if I didn't make a mistake ...
Honestly, the first one won't do anything though. Maybe I'm misunderstanding ... b/c I'm updating it to the ID of the SALES category ... where it's already the sales category.
I think you actually need only the INSERT statement.
Honestly, the first one won't do anything though. Maybe I'm misunderstanding ... b/c I'm updating it to the ID of the SALES category ... where it's already the sales category.
I think you actually need only the INSERT statement.
Update mmproductgroupdetail INNER Join MMPRODUCTCATEGORY
on MMProductGroupDetail.FKCategoryID = <CATEGORY ID OF SALE> AND
MMProductGroupDetail.FKProductID = mmproductgroupdetail.FKProductid
SET MMProductGroupDetail.FKCategoryID = <CATEGORY ID OF SALE>
where mmproductgroupdetail.price <=9.99
insert into mmproductcategory(Fkproductid,fkcategoryid)
(select distinct(mmproductgroupdetail.FKProductid),<CATEGORY ID OF SALE>
from mmproductgroupdetail Left Join MMPRODUCTCATEGORY
on MMProductGroupDetail.FKCategoryID = <CATEGORY ID OF SALE> AND
MMProductGroupDetail.FKProductID = mmproductgroupdetail.FKProductid
where mmproductgroupdetail.price <=9.99
AND MMProductGroupDetail.PKMMCategoryID IS NULL)
ASKER
Hrm, I see what you're getting at, but the structure is a bit more limiting than that I'm afraid. The only common column mmproductgroupdetail shares with mmproductcategory is fkproductid. :/ So we'd need a way to check against the fkproductid + category id of sale (which is 64 in this case) combination.
Something like:
IF($fkproductid, 64) EXISTS
DO
NOTHING
ELSE
insert into mmproductcategory(.....)
Obviously not nearly that simple, that's my thinking process on the logic flow.
You are right about the first one though, no need for an update, just the insert, now that I look at it.
It's an interesting problem because there's no secondary reference to check against the categoryid that I can see. I'm far from an expert on the subject just yet though.
Something like:
IF($fkproductid, 64) EXISTS
DO
NOTHING
ELSE
insert into mmproductcategory(.....)
Obviously not nearly that simple, that's my thinking process on the logic flow.
You are right about the first one though, no need for an update, just the insert, now that I look at it.
It's an interesting problem because there's no secondary reference to check against the categoryid that I can see. I'm far from an expert on the subject just yet though.
Try my INSERT statement then ... the LEFT JOIN ... Where PK... is NULL does the same thing as an If Not Exists -- but in set-based logic.
ASKER
My apologies if I'm missing something -- what would I join on, since mmproductgroupdetail doesn't have fkcategoryid?
Oh ... I guess the join needs to get a bit more complex. What is the relation between mmproductgroupdetail and mmproductcategory ?
Is there another table involved there?
Is there another table involved there?
ASKER
The relation is between the FKProductID. Basically the product ID is what links everything. mmproductgroupdetail relates to the primary key of product table, the fkproductid of mmproductcategory table, and several other smaller tables, like a bin_location table etc through the fkproductid tag.
The flow for categories is ->
Category table:
PKCategoryID
CategoryName
FKCategoryID (relates to itself for sub categories)
Product:
PKProductID
assorted attributes
MMProductGroupDetail:
FKProductID relating to product.pkproductid
SKU
Price
Quantity
and related attributes
And mmproductcategory has:
PKMMCategoryID
FKCategoryID
FKProductID
Essentially, since I already know the categoryid of sale, the join will be between the mmproductgroupdetail and mmproductcategory I think, the difficult part (at least, for me) is finding which records exist since there's no reference to category in mmproductgroupdetail as well.
The flow for categories is ->
Category table:
PKCategoryID
CategoryName
FKCategoryID (relates to itself for sub categories)
Product:
PKProductID
assorted attributes
MMProductGroupDetail:
FKProductID relating to product.pkproductid
SKU
Price
Quantity
and related attributes
And mmproductcategory has:
PKMMCategoryID
FKCategoryID
FKProductID
Essentially, since I already know the categoryid of sale, the join will be between the mmproductgroupdetail and mmproductcategory I think, the difficult part (at least, for me) is finding which records exist since there's no reference to category in mmproductgroupdetail as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Daniel,
Sorry for the late reply, I got side tracked on another project. Yes, that did the trick, and makes sense.
Thank you!
Sorry for the late reply, I got side tracked on another project. Yes, that did the trick, and makes sense.
Thank you!
ASKER
Thanks again!
In this case, however, I think you need to do an Update using an INNER join (to update existing) followed by an INSERT using an OUTER join (to insert those that don't already exist).
Want code for this kind of logic?