multi table query with cross reference table

The problem,

I have two tables that need to be 'matched up' from data in a third table

I am currently using a contains query - but I think the final part should be a subquery - just not sure of how to go about it.

I dont need any 'data' from the third table - just using it to compare some name (string field)

Any suggestions would be awesome...

SELECT Matches....

Seasons...

OpMatches...

FROM matches Matches

LEFT JOIN seasons Seasons ON (Seasons.id = (Matches.season_id) AND Seasons.stage_id = (Matches.stage_id))

LEFT JOIN op_matches OpMatches ON (Matches.match_date = OpMatches.match_date) /* AND 

(Matches.team_home = t.op_team_name OR Matches.team_home = OpMatches.team_home OR Matches.team_away = t.op_team_name OR Matches.team_away = OpMatches.team_away))  */

WHERE Matches.odds_url is null

AND(  

(Matches.team_home = OpMatches.team_home OR Matches.team_away = OpMatches.team_away)

    /*need the other stuff to compare team names - see the query below 

OR (Matches.team_home IN (select t.op_team_name from Teams t where t.ws_team_name=Matches.team_home and t.op_team_name=OpMatches.team_home) )

OR (Matches.team_away IN (select t.op_team_name from Teams t where t.ws_team_name=Matches.team_away and t.op_team_name=OpMatches.team_away) )

    */

)

/* the query im using now */

        $query->where(['Matches.odds_url is null'])

            ->contain(['OpMatches' => [

                'foreignKey' => false,  //turn off foreignKey when doing initial matching - data is not matched yet

                'queryBuilder' => function($q){

                    return $q->where([

                        'Matches.match_date = OpMatches.match_date',

                    ])

                    ->andWhere(function($exp){

                        return $exp->or_([

                            'Matches.team_home = t.op_team_name' ,

                            'Matches.team_home = OpMatches.team_home' ,

                            'Matches.team_away = t.op_team_name' ,

                            'Matches.team_away = OpMatches.team_away']);

                    });

                }//end - querybuilder                     

            ],

                    //'MatchesCsv',  //this gets turned on later - not needed now

                    'Seasons'   //load the season object too - we need the league id

            ])

            ->join([    //not sure a join is the right to go - i dont need the data - just using columns to compare string fields

                'table' => 'teams',

                'alias' => 't',

                'type' => 'LEFT',

                'conditions' => ['t.league_id' => 'Seasons.league_id' , 

                    '(t.ws_team_name = Matches.team_home OR t.ws_team_name = Matches.team_away)']

            ]);        

        return $query;

I apologize if the problem isn't clear.

I can try to explain more in depth if anyone needs.

Thanks,

Joe

p.s.

i will set a .001 btc  bounty for solution

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.