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.
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

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