Solved

Using condition logic with MySQL SQL statement syntax?

Posted on 2008-10-13
11
797 Views
Last Modified: 2013-12-13
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

0
Comment
Question by:jmoriarty
  • 6
  • 5
11 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22703353
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
 

Author Comment

by:jmoriarty
ID: 22703677
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22703941
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
 

Author Comment

by:jmoriarty
ID: 22704184
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22704280
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:jmoriarty
ID: 22705116
My apologies if I'm missing something -- what would I join on, since mmproductgroupdetail doesn't have fkcategoryid?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22705401
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
 

Author Comment

by:jmoriarty
ID: 22705971
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 22710915
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
 

Author Comment

by:jmoriarty
ID: 22825940
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
 

Author Closing Comment

by:jmoriarty
ID: 31407081
Thanks again!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now