Join query for optimization?

[Arianna]

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

[Seapyramid]

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

[Arianna]

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