complicated query, logic help needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

complicated query, logic help needed

Post by invisibled »

Hey All,

I'll provide as much info as i can think of and just ask me anything if you need more info.

Ok so i have information in 3 tables, see below for the columns. The info i will be printing out will be from the 'Items' table. I need the 'Jetting' table to get the year and the 'application_mappings' table to link the jetting_id with the item_id.

So, i need a query (or multiple query's) to select a product from 2010 (Jetting table) then link the id's of that back to the Items table to display all the items from 2010. I'm working with an existing system so no tables can be changed in any way.

Anything else let me know, thanks for your input!

application_mappings

Code: Select all

 
Field   Type    Null    Default     Comments
map_id  int(11)     No           
jetting_id  int(11)     Yes     NULL     
item_id     varchar(255)    Yes     NULL     
map_created     datetime    Yes     NULL     
map_updated     datetime    Yes     NULL     
 
Items

Code: Select all

 
Field   Type    Null    Default     Comments
ItemID  varchar(255)    No           
IMARecordID     varchar(25)     Yes     NULL     
IMAName     varchar(255)    Yes     NULL     
ProductFamily   varchar(255)    Yes     NULL     
Name    varchar(255)    Yes     NULL     
Price   decimal(10,0)   Yes     NULL     
SalePrice   decimal(10,0)   Yes     NULL     
Finish  varchar(50)     Yes     NULL     
Construction    varchar(50)     Yes     NULL     
ItemStatusCode  varchar(50)     Yes     NULL     
Color   varchar(50)     Yes     NULL     
Size    varchar(50)     Yes     NULL     
PoundsLighterThanStock  decimal(10,0)   Yes     NULL     
Weight  decimal(10,0)   Yes     NULL     
Description     text    Yes     NULL     
CartImage   varchar(255)    Yes     NULL     
ThumbnailImage  varchar(255)    Yes     NULL     
DetailsImage    varchar(255)    Yes     NULL     
ZoomImage   varchar(255)    Yes     NULL     
Reorder     int(11)     Yes     NULL     
AdjustRate  varchar(255)    Yes     NULL     
OriginalImage   varchar(255)    Yes     NULL     
Stock   int(11)     Yes     NULL     
WholePrice  decimal(10,0)   Yes     NULL     
SoftGoodFileName    varchar(50)     Yes     NULL     
Disabled    bit(1)  No           
Installation_Notes  bit(1)  No           
Dyno_Charts     bit(1)  No           
Tech_Tips   bit(1)  No           
Marketing_Description   text    Yes     NULL     
Images  bit(1)  No           
IMALastModifiedDate     datetime    Yes     NULL     
IMAAttribVehicle    varchar(50)     Yes     NULL     
IMAAttribStroke     varchar(50)     Yes     NULL     
IMAAttribType   varchar(50)     Yes     NULL     
IMAAttribConfiguration  varchar(50)     Yes     NULL     
IMAAttribMufflerConstruction    varchar(50)     Yes     NULL     
IMAAttribMufflerColor   varchar(50)     Yes     NULL     
IMAAttribMidpipeConstruction    varchar(50)     Yes     NULL     
IMAAttribEndcapConstruction     varchar(50)     Yes     NULL     
IMAAttribSparkArrestor  varchar(50)     Yes     NULL     
IMAAttribQuietInsert    varchar(50)     Yes     NULL     
IMAAttribHeaderConstruction     varchar(50)     Yes     NULL     
IMAAttribHeaderType     varchar(50)     Yes     NULL     
IMAAttribFitment    varchar(50)     Yes     NULL     
IMAAttribVersion    varchar(50)     Yes     NULL     
IMAAttribSoundLevel     varchar(50)     Yes     NULL     
IMAAttribWeightComparedtoStock  varchar(50)     Yes     NULL     
IMAAttribApparelStyle   varchar(50)     Yes     NULL     
IMAAttribApparelType    varchar(50)     Yes     NULL     
IMAAttribApparelSize    varchar(50)     Yes     NULL     
IMAAttribApparelColor   varchar(50)     Yes     NULL     
IMAAttribCreateDate     varchar(50)     Yes     NULL     
 
Jetting

Code: Select all

 
Field   Type    Null    Default     Comments
ID  int(11)     No           
ManufacturerURL     varchar(250)    Yes     NULL     
OtherURL    varchar(250)    Yes     NULL     
TextBody    varchar(4000)   Yes     NULL     
TYPE    varchar(255)    Yes     NULL     
MAKE    varchar(255)    Yes     NULL     
MODEL   varchar(255)    Yes     NULL     
YEAR    varchar(255)    Yes     NULL     
MAIN_JET    varchar(255)    Yes     NULL     
PILOT_JET   varchar(255)    Yes     NULL     
NEEDLE  varchar(255)    Yes     NULL     
NEEDLE_POSITION     varchar(255)    Yes     NULL     
POWER_JET   varchar(255)    Yes     NULL     
AIR_SCREW   varchar(255)    Yes     NULL     
FUEL_SCREW  varchar(255)    Yes     NULL     
COMMENTS    varchar(255)    Yes     NULL     
 
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: complicated query, logic help needed

Post by mikosiko »

more than one way to do it... here is one possibility... read also about JOIN (all its forms) and views

Code: Select all

 
SELECT a.* FROM items AS a
WHERE a.ItemID IN (SELECT b.item_id FROM applications_mappings AS b 
                                                            JOIN jetting AS c ON b.jetting_id = c.ID 
                                    WHERE c.year = 2010)
 
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: complicated query, logic help needed

Post by invisibled »

oh wow thanks so much! the query was just hurting my brain. When i put the query in my code like so

Code: Select all

 
$sql = mysql_query(" SELECT a.* FROM items AS a WHERE a.ItemID IN (SELECT b.item_id FROM application_mappings AS b JOIN jetting AS c ON b.jetting_id = c.ID WHERE c.year = 2010) LIMIT 10 ") or die(mysql_error());
 
it times out mysql, giving me the "MySQL server has gone away" error. Know why it would be doing that? I'm working on pipointing the source right now.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: complicated query, logic help needed

Post by mikosiko »

before to include the select in your php I will test it using MysqlQuery or similar to be sure that works as expected.

How many record do you have in each table?
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: complicated query, logic help needed

Post by invisibled »

just ran the query in phpmyadmin. It does the same thing, just makes the page load until the timeout occurs.

application_mappings has 16,973 records
Items has 5,241 records
Jetting has 2,426 records
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: complicated query, logic help needed

Post by mikosiko »

some suggestions:

- Read http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
- Try to optimize the query using JOIN differently (read the manual for the select clause ans JOIN options)... as I said... is more than one way to solve your query... in particular the way that I did provide is using a Subquery which no necessarily is the best option.

- Try also using a View

- Be sure that your tables has Indexes
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: complicated query, logic help needed

Post by Eran »

That's a dependant subquery which would run very slowly with many rows. A JOIN would be preferred, something like:

Code: Select all

SELECT items.* FROM items
INNER JOIN application_mappings AS am ON am.item_id=items.ItemID
INNER JOIN jetting ON jetting.ID = am.jetting_id
WHERE jetting.`YEAR` = '2010'
If it's still running slowly run EXPLAIN on it and post the results here
Last edited by Eran on Wed Mar 31, 2010 11:18 am, edited 2 times in total.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: complicated query, logic help needed

Post by mikosiko »

which would run very slowly with many rows
and specially without proper Indexing.... totally agree with pytrin... hence my suggestions to read about JOINS

the first "ON" should be an "AS" if I'm not reading incorrectly

Code: Select all

 
INNER JOIN jetting [color=#FF0000]ON[/color] jetting ON jetting.ID
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: complicated query, logic help needed

Post by Eran »

Thanks, corrected.
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: complicated query, logic help needed

Post by invisibled »

hey pytrin,

I'm not exactly sure what you want me to do with the code you posted? could you explain a little better?

just fyi, i'm really behind on the whole JOINS thing, never really done it at all.
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: complicated query, logic help needed

Post by invisibled »

Code: Select all

$sql = mysql_query(" SELECT 
				Items.* FROM Items 
				INNER JOIN application_mappings AS am ON am.item_id=items.ItemID 
				INNER JOIN Jetting ON Jetting.ID = am.jetting_id 
				WHERE Jetting.`YEAR` = '2010' AND Items.ProductFamily = 'Mini Moto'
				GROUP BY Items.ProductFamily
				ORDER BY Items.ProductFamily ASC
				LIMIT 2
			");
this is what i ended up with and it works! Thank you very much to everybody that helped :D
Post Reply