compound select query

[kpjuni]

Ok - in a nut shell. Im trying to grab all the information out of several fields from several tables from a single database. now.

I've got all that working, BUT. i want to grab all the information from all those tables except i want to single down on one field for a certain object.. UGH this is hard to put into words.. let me put in my code and see if you can figure out what im doing.. then i'll explain what i'm trying to get out of it!

 

$query =  ' SELECT rr.individual_id as id, rr.rank, rr.bonus_points, srt.points_attribution '
                . ' FROM #__tracks_rounds_results AS rr '
                . ' INNER JOIN #__tracks_projects_subrounds AS sr ON sr.id = rr.subround_id '
                . ' INNER JOIN #__tracks_subroundtypes AS srt ON srt.id = sr.type '
                . ' INNER JOIN #__tracks_projects_rounds AS pr ON pr.id = sr.projectround_id '
                . ' WHERE pr.project_id = ' . $project_id
                ;
                
        $this->_db->setQuery( $query );
        return $this->_db->loadObjectList();

 

Ok, to make it a bit simpler. I'm wanting to add to the 'WHERE' statement ".. AND sr.type = 11".

now.. if i do that, it will pull ALL the information where sr.type = 11.. i dont want that.. i want it to only pull "rr.rank" where sr.type = 11

 

is anyone following this? lol

is this even fricken possible? haha - i've been reading around about compound select queries but i can't get any of them to work, so.. i figured i would put my actual code out there and see if you guys can decifer it and tell me what i need to do.

 

THANKS!

[fenway]

Well, if it's just a one to one relationship, then you can add this to the ON condition.. otherwise, you have to use a derived table.