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!


//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)

Open in new window

jmoriartyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
If you could afford to check the Primary Key of the table, then, yes.  The Insert ... On Duplicate Key Update ... syntax is very cool.

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?
0
jmoriartyAuthor Commented:
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
0
Daniel WilsonCommented:
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.

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)

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

jmoriartyAuthor Commented:
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.
0
Daniel WilsonCommented:
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.
0
jmoriartyAuthor Commented:
My apologies if I'm missing something -- what would I join on, since mmproductgroupdetail doesn't have fkcategoryid?
0
Daniel WilsonCommented:
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?
0
jmoriartyAuthor Commented:
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.
0
Daniel WilsonCommented:
OK, but we're putting all products  w/ a price < 9.99 in the "sale" category ...

How's this one?


insert into mmproductcategory(Fkproductid,fkcategoryid) 
(select distinct(Product.FKProductid),<CATEGORY ID OF SALE> 
from Product Left Join MMPRODUCTCATEGORY 
        on mmproductcategory.FKCategoryID = <CATEGORY ID OF SALE> AND
        Product.PKProductID = mmproductcategory.FKProductid
where Product.price <=9.99
        AND mmproductcategory.PKMMCategoryID IS NULL);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmoriartyAuthor Commented:
Hi Daniel,

Sorry for the late reply, I got side tracked on another project. Yes, that did the trick, and makes sense.

Thank you!

0
jmoriartyAuthor Commented:
Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.