Solved

Using condition logic with MySQL SQL statement syntax?

Posted on 2008-10-13
11
798 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

947 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

20 Experts available now in Live!

Get 1:1 Help Now