Mysidia Adoptables Support Forum - Join query for optimization?

Mysidia Adoptables Support Forum (http://www.mysidiaadoptables.com/forum/index.php)

- Questions and Supports (http://www.mysidiaadoptables.com/forum/forumdisplay.php?f=18)

- - Join query for optimization? (http://www.mysidiaadoptables.com/forum/showthread.php?t=1384)

Join query for optimization?

I'm trying to make so siggy.php (function getcurrentimage($id)) uses one join query.
Now, this query has been tweaked and tweaked and tweaked, but it gets too many rows from the db. My original query worked fine if it wasn't an egg, but if it was (level=0) then it selected nothing at all.

PHP Code:


		
			
SELECT *                                                                       // easy, it just selects

FROM adopts_adoptables, adopts_owned_adoptables, adopts_levels                // where it selects from

WHERE  `adopts_owned_adoptables`.`aid` = 144 AND                              // where the adoptable id is the one we want

`adopts_owned_adoptables`.`type` = `adopts_adoptables`.`type` AND             // where the adopts type is the type we select from adopts_adoptables

(`adopts_levels`.`adoptiename` = `adopts_adoptables`.`type` OR                // either    (a) the level's type name is equal to the adopt's type

`adopts_owned_adoptables`.`currentlevel` = 0) AND                            // or        (b) the adopt's current level is equal to 0

(`adopts_owned_adoptables`.`currentlevel` = `adopts_levels`.`thisislevel`    // and either    (a) the adopt's current level is the level we select from the db

 OR `adopts_owned_adoptables`.`currentlevel` = 0)                            // or            (b) the adopt's current level is equal to 0

LIMIT 1                                                                        // we only want one adoptable

Any idea on what I should do? I want to use a join query as these images are used so much, but it's so complicated. :P

RE: Join query for optimization?

The query is wrong. You are selecting from multiple tables.. but not telling it what to look for in what table.
FROM ".$prefix."owned_adoptables AS a JOIN

PHP Code:


		
			
SELECT *                                                                       
FROM adopts_adoptables AS a JOIN adopts_owned_adoptables AS b ON  a.aid=b.aid 
JOIN adopts_levels  AS c ON a.aid=c.aid           
WHERE  `a.aid` = 144 AND                            
`a.type` = `adopts_adoptables`.`type` AND          
(`adopts_levels`.`adoptiename` = `adopts_adoptables`.`type` OR                
`adopts_owned_adoptables`.`currentlevel` = 0) AND                       
(`adopts_owned_adoptables`.`currentlevel` = `adopts_levels`.`thisislevel`
 OR `adopts_owned_adoptables`.`currentlevel` = 0)                            
LIMIT 1

This isn't fully complete.. but you can see my changes & work with the pattern on what the WHERE is.

Sea

RE: Join query for optimization?

Thank you! :) I had gotten around it by adding a level 0 for each adoptable, but this will do much better. x3