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+unsubscr...@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.