stb74

Not sure whether this will help any but I thought that after I generate the table data I sort the league table on $PPoints ( Previous weeks points) I maybe run the data through a foreach and add in a Position number for each team.

Not sure whether the subject is correct but hopefully I can explain it ok. I have a football/soccer League table that after I generate I run a foreach to display the league table, within this I add a position counter. I end up with something like this Position Team P W D L F A Pts 1 Malachians 11 6 3 2 24 16 21 2 Killyleagh YC 7 5 1 1 16 9 16 3 Newington YC 7 4 3 0 21 12 15 4 Comber Rec 9 4 3 2 20 22 15 5 Wellington Rec 8 3 3 2 17 16 12 I am now looking to add another position column but show the previous weeks position. This is my code to generate the table data foreach ($query->result() as $team) { $_wins = $this->getWins($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_draws = $this->getDraws($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_previous_wins = $this->getPreviousWins($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_previous_draws = $this->getPreviousDraws($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_loses = $this->getLoses($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_goalsfor = $this->getGoalsFor($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_goalsagainst = $this->getGoalsAgainst($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_goaldiff = ($_goalsfor - $_goalsagainst ); $_played = ($_wins + $_draws + $_loses); $_deductions = $this->getDeductions($team->ID_TEAM,$ID_DIVISION,$ID_SEASON); $_tpoints = $_deductions + (($_wins*$_points_for_win) + ($_draws*$_points_for_draw)); $_previous_points = $_deductions + (($_previous_wins*$_points_for_win) + ($_previous_draws*$_points_for_draw)); $_data[$team->ID_TEAM] = array("ID_TEAM"=> $team->ID_TEAM, "team_name"=> $team->team_name, "Played"=> $_played, "Wins"=> $_wins, "Draws"=> $_draws, "Loses"=> $_loses, "GoalsFor"=> $_goalsfor, "GoalsAgainst"=>$_goalsagainst, "GoalDiff"=> $_goaldiff, "Points"=> $_tpoints, "PPoints"=> $_previous_points, "Deductions"=> $_deductions ); } $_data = sortOldTable($_data);

I had to move onto other things, but now I am back to get this working. bluejay002 I do have a table to maintain history and use seasonID to link them together Table 1 ID_DIVISION division_name abbrev Link Table ID_LINK ID_DIVISION ID_SEASON ID_LEAGUE division_line I created the link table as each season the division_line can change, this new table allowed me to cater for that. But as I said before now with some restructuring the division_name can change. What I have tried to do is create another table, name Link Table ID_LINK ID_DIVISION ID_SEASON division_name I have one entry in here and it contains the new name for the division I wanted changed. I could probably combine the two link tables. With the following query SELECT d.division_name, dc.division_name FROM divisions d LEFT JOIN division_changes dc ON ( d.ID_DIVISION = dc.division_id AND dc.season_id = '4' ) JOIN division_details dt ON ( d.ID_division = dt.ID_DIVISION ) JOIN leagues L ON ( d.ID_LEAGUE = L.ID_LEAGUE ) WHERE DT.ID_LEAGUE = '9' AND DT.ID_SEASON = '4' Returns d.division_name dc.division_name Premier League IFA Premiership First Division IFA Championship Reserve League NULL IFA Youth Section A NULL IFA Youth Section B NULL Which is what I am looking for, but my question now is, can I return d.division_name for the ones that don't have a dc.division_name and vice versa. Or do I have to use php to display the appropriate names So I am returning division_name IFA Premiership IFA Championship Reserve League IFA Youth Section A IFA Youth Section B

Not to worry hopefully I get this right my version in brackets, French Championships(league) Elite (Division) Nationale A (Division) Nationale B (Division) Each Season the names do not change, this was like my system there had been no changes, then the last two seasons the league has decided to restructure and rename the Elite Nationale A and remove Nationale B. The problem I have is that if I change the name it means that for each season the name will be the new name, what I would like to have is last season it stays Elite and Nationale A and this season I can have the new name.

Zwiter Do you follow football

I have a soccer/football website and have a DB table for divisions ID_DIVISION ID_LEAGUE division_name abbrev divisions_line 1 1 Premier Division PremierDivision 1,12 The names and division lines never changed up until last year so I added another DB table called division_details ID_LINK ID_DIVISION ID_SEASON ID_LEAGUE division_line The divisions DB table hasa structure of ID_DIVISION division_name abbrev This enables me to use the division_details DB table to have different data each season as with new seasons there may be more divisions or the division_lines may be different each season. What has happened this year is that One of the leagues has changed its division structure which means that if I change the name of the division under the divisions DB table, it will change the name for all seasons. What I am looking help for is the best way to have the DB tables, I can move the division_name and abbrev into the division_details DB but then that will leave one field in divisions. Apologies about the explaination.

I always wondered why that was doing it that way now I understand. I am building up quite a large stats site for football teams and I have still a long way to go LOL. ??? Thanks for the help.

I do seem to make things a little harder for myself, particular on my site.

Excellent easy when you know how.

To save you some hassle, I have done a compromise but I am not sure whether its the best way to do it. I have kept both queries and then combined them using array_merge.

My first query will get the list of 11 players that started the game, the second query will get the list of substitutes. I am looking to build a query that will give me the one list.

I am trying to get ID_Player, Firstname and Lastname from the core_players table for the players that have played in a particular match (23000). These players will be in the starting 11 (core_player_appearances) or subs (core_player_substitutes).

-- -------------------------------------------------------- -- -- Table structure for table `core_player_appearances` -- CREATE TABLE `core_player_appearances` ( `ID_APPEARANCE` int(10) unsigned NOT NULL auto_increment, `ID_SEASON` mediumint(10) NOT NULL default '0', `ID_MATCH` int(10) NOT NULL default '0', `ID_TEAM` int(10) NOT NULL default '0', `ID_PLAYER` int(10) NOT NULL default '0', PRIMARY KEY (`ID_APPEARANCE`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=772 ; -- -- Dumping data for table `core_player_appearances` -- INSERT INTO `core_player_appearances` VALUES (761, 2, 23000, 405, 11); INSERT INTO `core_player_appearances` VALUES (762, 2, 23000, 405, 30); INSERT INTO `core_player_appearances` VALUES (763, 2, 23000, 405, 9); INSERT INTO `core_player_appearances` VALUES (764, 2, 23000, 405, 33); INSERT INTO `core_player_appearances` VALUES (765, 2, 23000, 405, 24); INSERT INTO `core_player_appearances` VALUES (766, 2, 23000, 405, 16); INSERT INTO `core_player_appearances` VALUES (767, 2, 23000, 405, 28); INSERT INTO `core_player_appearances` VALUES (768, 2, 23000, 405, 41); INSERT INTO `core_player_appearances` VALUES (769, 2, 23000, 405, 2); INSERT INTO `core_player_appearances` VALUES (770, 2, 23000, 405, 12); INSERT INTO `core_player_appearances` VALUES (771, 2, 23000, 405, 4); -- -------------------------------------------------------- -- -- Table structure for table `core_player_substitutes` -- CREATE TABLE `core_player_substitutes` ( `ID_SUBSTITUTE` int(10) unsigned NOT NULL auto_increment, `ID_SEASON` mediumint(10) NOT NULL default '0', `ID_MATCH` int(10) NOT NULL default '0', `ID_TEAM` int(10) NOT NULL default '0', `ID_PLAYER` int(10) NOT NULL default '0', PRIMARY KEY (`ID_SUBSTITUTE`), KEY `ID_SEASON` (`ID_SEASON`,`ID_MATCH`,`ID_PLAYER`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=147 ; -- -- Dumping data for table `core_player_substitutes` -- INSERT INTO `core_player_substitutes` VALUES (144, 2, 23000, 405, 35); INSERT INTO `core_player_substitutes` VALUES (145, 2, 23000, 405, 39); INSERT INTO `core_player_substitutes` VALUES (146, 2, 23000, 405, ; -- -------------------------------------------------------- -- -- Table structure for table `core_players` -- CREATE TABLE `core_players` ( `ID_PLAYER` int(10) NOT NULL auto_increment, `ID_SEASON` mediumint(10) NOT NULL default '0', `ID_CLUB` int(10) NOT NULL default '0', `firstname` varchar(128) NOT NULL default '', `lastname` varchar(128) NOT NULL default '', `number` tinyint(3) NOT NULL default '0', `position` varchar(10) NOT NULL default '', `profile` text NOT NULL, `image` text NOT NULL, `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ID_PLAYER`), KEY `ID_CLUB` (`ID_CLUB`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=59 ; -- -- Dumping data for table `core_players` -- INSERT INTO `core_players` VALUES (1, 2, 1, 'Stephen', 'Hewitt', 0, 'Goalkeeper', 'Egit', '', '2008-02-26 11:33:30'); INSERT INTO `core_players` VALUES (2, 2, 1, 'Stewart', 'Finn', 0, 'Defender', '', '', '2008-02-09 07:03:38'); INSERT INTO `core_players` VALUES (3, 2, 1, 'Ricky', 'Shuttleworth', 0, 'Defender', '', '', '2008-02-09 07:03:58'); INSERT INTO `core_players` VALUES (4, 2, 1, 'Gavin', 'Fox', 0, 'Defender', '', '', '2008-02-09 07:04:07'); INSERT INTO `core_players` VALUES (5, 2, 1, 'John', 'Hunter', 0, 'Midfielder', '', '', '2008-02-09 07:04:17'); INSERT INTO `core_players` VALUES (6, 2, 1, 'Chris', 'Monahan', 0, 'Midfielder', '', '', '2008-02-09 07:04:46'); INSERT INTO `core_players` VALUES (7, 2, 1, 'Tomas', 'McCann', 0, 'Midfielder', '', '', '2008-02-09 07:05:01'); INSERT INTO `core_players` VALUES (8, 2, 1, 'Eamon', 'McCann', 0, 'Midfielder', '', '', '2008-02-09 07:05:12'); INSERT INTO `core_players` VALUES (9, 2, 1, 'Chris', 'Carberry', 0, 'Forward', '', '', '2008-02-09 07:05:27'); INSERT INTO `core_players` VALUES (10, 2, 1, 'Peter', 'Sharvin', 0, 'Forward', '', '', '2008-02-09 07:05:41'); INSERT INTO `core_players` VALUES (11, 2, 1, 'Chris', 'Beattie', 0, 'Midfielder', '', '', '2008-02-09 07:05:57'); INSERT INTO `core_players` VALUES (12, 2, 1, 'John', 'Finnegan', 0, 'Forward', '', '', '2008-02-09 07:06:18'); INSERT INTO `core_players` VALUES (13, 2, 1, 'Martin', 'McKenna', 0, 'Defender', '', '', '2008-02-09 07:07:37'); INSERT INTO `core_players` VALUES (14, 2, 1, 'Sean', 'Tunney', 0, 'Defender', 'Club Trainer', '', '2008-02-27 05:03:24'); INSERT INTO `core_players` VALUES (15, 2, 1, 'Niall', 'McNulty', 0, 'Midfielder', '', '', '2008-02-09 07:06:55'); INSERT INTO `core_players` VALUES (16, 2, 1, 'Paul', 'Daly', 0, 'Midfielder', '', '', '2008-02-09 07:07:54'); INSERT INTO `core_players` VALUES (17, 2, 1, 'Micheal', 'McCann', 0, 'Forward', '', '', '2008-02-09 07:08:02'); INSERT INTO `core_players` VALUES (18, 1, 1, 'Paul', 'McMullan', 0, 'G', '', '', '2008-02-04 15:12:56'); INSERT INTO `core_players` VALUES (19, 1, 1, 'James', 'Oguz', 0, 'F', '', '', '2008-02-04 15:13:30'); INSERT INTO `core_players` VALUES (20, 1, 1, 'Steven', 'Hollywood', 0, 'F', '', '', '2008-02-04 15:14:09'); INSERT INTO `core_players` VALUES (21, 1, 1, 'Andrew', 'Mitchell', 0, 'M', '', '', '2008-02-04 15:14:52'); INSERT INTO `core_players` VALUES (22, 2, 1, 'Phil', 'McManus', 0, 'D', '', '', '2008-02-04 04:45:19'); INSERT INTO `core_players` VALUES (23, 2, 1, 'Michael', 'Teague', 0, 'M', '', '', '2008-02-04 04:48:32'); INSERT INTO `core_players` VALUES (24, 2, 1, 'Ronan', 'Cullen', 0, 'M', '', '', '2008-02-04 04:50:03'); INSERT INTO `core_players` VALUES (25, 2, 1, 'Ciaran', 'Gorman', 0, 'M', '', '', '2008-02-04 16:50:28'); INSERT INTO `core_players` VALUES (26, 2, 1, 'Marc', 'McGerty', 0, 'D', '', '', '2008-02-04 16:51:02'); INSERT INTO `core_players` VALUES (27, 1, 1, 'Brian', 'Teggart', 0, 'G', '', '', '2008-02-04 16:51:24'); INSERT INTO `core_players` VALUES (28, 2, 1, 'Eamonn', 'Devlin', 0, 'M', '', '', '2008-02-04 16:51:51'); INSERT INTO `core_players` VALUES (29, 1, 1, 'Emmanuel', 'Wilkinson', 0, 'F', '', '', '2008-02-04 16:52:16'); INSERT INTO `core_players` VALUES (30, 2, 1, 'Pablo', 'Berrueco', 0, 'F', '', '', '2008-02-04 16:52:35'); INSERT INTO `core_players` VALUES (31, 2, 1, 'Brian', 'Madden', 0, 'D', '', '', '2008-02-04 16:52:58'); INSERT INTO `core_players` VALUES (32, 2, 1, 'Mark', 'Mullan', 0, 'D', '', '', '2008-02-04 16:53:23'); INSERT INTO `core_players` VALUES (33, 2, 1, 'Mark', 'Coyles', 0, 'D', '', '', '2008-02-04 16:53:48'); INSERT INTO `core_players` VALUES (34, 2, 1, 'Joe', 'McKevitt', 0, 'D', '', '', '2008-02-04 16:54:16'); INSERT INTO `core_players` VALUES (35, 2, 1, 'Peter', 'Johnston', 0, 'F', '', '', '2008-02-04 17:04:28'); INSERT INTO `core_players` VALUES (36, 2, 1, 'Niall', 'McLoughlin', 0, 'M', '', '', '2008-02-04 05:05:07'); INSERT INTO `core_players` VALUES (37, 2, 1, 'Conor', 'O''Neill', 0, 'M', '', '', '2008-02-04 17:05:41'); INSERT INTO `core_players` VALUES (38, 2, 1, 'David', 'Hamilton', 0, 'F', '', '', '2008-02-04 17:06:20'); INSERT INTO `core_players` VALUES (39, 2, 1, 'Joe', 'Kennedy', 0, 'D', '', '', '2008-02-04 17:06:53'); INSERT INTO `core_players` VALUES (40, 1, 1, 'Cormac', 'McGarry', 0, 'M', '', '', '2008-02-04 17:07:43'); INSERT INTO `core_players` VALUES (41, 2, 1, 'David', 'Doherty', 0, 'G', '', '', '2008-02-04 17:08:08'); INSERT INTO `core_players` VALUES (42, 2, 1, 'Conor', 'McSteen', 0, 'F', '', '', '2008-03-16 18:50:07'); INSERT INTO `core_players` VALUES (43, 2, 1, 'Steve', 'Moore', 0, 'D', '', '', '2008-02-04 17:09:31'); INSERT INTO `core_players` VALUES (44, 1, 1, 'Barry', 'Meehan', 0, 'F', '', '', '2008-02-04 17:12:38'); INSERT INTO `core_players` VALUES (45, 2, 7, 'R', 'Galbraith', 0, '', '', '', '2008-02-29 13:10:26'); INSERT INTO `core_players` VALUES (46, 2, 7, 'S', 'Williamson', 0, '', '', '', '2008-02-29 13:10:26'); INSERT INTO `core_players` VALUES (47, 2, 7, 'T', 'Beckett', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (48, 2, 7, 'R', 'McComb', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (49, 2, 7, 'P', 'Magee', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (50, 2, 7, 'D', 'Gallagher', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (51, 2, 7, 'P', 'Murphy', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (52, 2, 7, 'G', 'Beattie', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (53, 2, 7, 'C', 'Rea', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (54, 2, 7, 'A', 'Cardwell', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (55, 2, 7, 'P', 'McKee', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (56, 2, 7, 'Sub', '1', 0, 'Midfielder', '', '', '2008-03-20 14:49:42'); INSERT INTO `core_players` VALUES (57, 2, 7, 'Sub', '2', 0, 'Forward', '', '', '2008-03-20 14:49:57'); INSERT INTO `core_players` VALUES (58, 2, 1, 'test', 'test', 0, 'Defender', '', '', '2008-03-21 21:02:39');

Too early in the morning for this but whats DDL.

If I run two query's individually I get the correct results but I need to return the results from one query but can't seem to get them joined correctly. The following will get 11 match players SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_appearances A ON ( P.ID_PLAYER = A.ID_PLAYER AND A.ID_SEASON =2 AND A.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 The following will get the match substitutes SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_substitutes S ON ( P.ID_PLAYER = S.ID_PLAYER AND S.ID_SEASON =2 AND S.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 I just can't seem to work out what the correct terminology is when combining the two, maybe you can't do it. SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_appearances A ON ( P.ID_PLAYER = A.ID_PLAYER AND A.ID_SEASON =2 AND A.ID_MATCH =22998 ) JOIN core_player_substitutes S ON ( P.ID_PLAYER = S.ID_PLAYER AND S.ID_SEASON =2 AND S.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 The Above returns nothing, can someone please point out my blinding mistake, cheers.

Thanks for the reply and effort. Got it sorted thanks.

I have a multi select script that allows me to transfer values from one multi select list to another. But I would like to be able to count the number of entries that I am adding to the second list and display this to a maximum of 11. I also need to display an error if I try and save this and there are less than 11 in the second list box. <script> $(document).ready(function() { $("#select_left").multiSelect("#select_right", {trigger: "#options_right"}); $("#select_right").multiSelect("#select_left", {trigger: "#options_left"}); $("#select_left2").multiSelect("#select_right2", {trigger: "#options_right2"}); $("#select_right2").multiSelect("#select_left2", {trigger: "#options_left2"}); }); </script> <table width="100%" border="0" cellpadding="0"> <tr> <td width="40%"> <select id="select_left" name="players[]" size="20" multiple="multiple"> <? foreach($players as $player): ?> <option value="<?=$player['ID_PLAYER'];?>"> <?=substr($player['firstname'], 0, 1);?> <?=strtoupper($player['lastname']);?> <? endforeach; ?> </select> <td style="text-align:center; vertical-align:middle;"> <a id="options_right" href="javascript:void(0);">Add >></a><br/><br/> <a id="options_left" href="javascript:void(0);"><< Remove</a> </td> <td width="40%"> <select id="select_right" name="match_players[]" size="20" multiple="multiple"> <? foreach($match_players as $player): ?> <option value="<?=$player['ID_PLAYER'];?>"> <?=$player['playername'];?> <? endforeach; ?> </select> <p id="players_selected"> Please pick 11 players from the list who played in this match. </p> </td> </tr> </table> </fieldset> <?=form_submit('submit', 'Save');?> <?=form_close();?> I have also played around with this code but it needs me to have the items in the list selected to work. <script type="application/javascript"> $(function() { var num_players = 0; $("#select_right").change(function(){ num_players = 0; $("#select_right").children().each(function(){ if($(this).attr('selected')) { num_players++; } }); $("#players_selected").html('You have selected ' + num_players + ' players.'); }); $("#players_form").submit(function(){ if(num_players != 11) { alert('Please select 11 players, you have currently selected ' + num_players + '.'); return false; } }); }); </script>

Think I have it, just to do some more testing. Cheers

Cheers that worked a treat. Could you possibly help with another query. With the query now displaying just the Opponents I would like if possible to either display whether the match was played at home or away. I may have to use some both php and some addition mysql code for this. For instance original code Location hometeam awayteam Away Killyleagh YC Albert Foundry Away Barn Utd Albert Foundry Home Albert Foundry Comber Rec Home Albert Foundry Kilmore Rec Away Newcastle Albert Foundry Away Civil Service Albert Foundry Home Albert Foundry Dunmurry Rec Home Albert Foundry Grove Utd Away Comber Rec Albert Foundry Away Holywood FC Albert Foundry Away Orangefield OB Albert Foundry Is this even possible, thanks

Its been a while since I have been able to look at this piece of code but I am going to have another go at it. Hopefully someone can help me. A refresher for you. I have a matches table, included in this are the matchHomeID(teamID) and matchAwayID(teamID) I want a query that will search the table for a particular team and display the results of those matches. My current code will display both the team I am searching for and the opposing team, but I only want it to return the opposing team Name. My current code is as follows SELECT ht.teamName AS hometeam, at.teamName AS awayteam FROM core_matches LM JOIN leaguestats_teams ht ON ( ht.teamID = LM.matchHomeID ) JOIN leaguestats_teams at ON ( at.teamID = LM.matchAwayID ) WHERE ( ht.teamID = '1' OR at.teamID = '1' ) AND LM.matchSeasonID = '4' ORDER BY LM.matchDate, hometeam This code will result in the following hometeam awayteam Killyleagh YC Albert Foundry Barn Utd Albert Foundry Albert Foundry Comber Rec Albert Foundry Kilmore Rec Newcastle Albert Foundry Civil Service Albert Foundry Albert Foundry Dunmurry Rec Albert Foundry Grove Utd Comber Rec Albert Foundry Holywood FC Albert Foundry Orangefield OB Albert Foundry Albert Foundry is teamID = 1, I would like to return just the opposing team. opponents Killyleagh YC Barn Utd Comber Rec Kilmore Rec Newcastle Civil Service Dunmurry Rec Grove Utd Comber Rec Holywood FC Orangefield OB Your help appreciated

Right I have it displaying the Cup Name and division Name for the relevant matches. Currently I ma displaying both the team I want details for as well as their opponents but I only want to display the team Name of who they play. SELECT ht.teamName AS hometeam, at.teamName AS awayteam, LM.matchHomeGoals AS goals_home, LM.matchAwayGoals AS goals_away, LM.matchReport AS report, LM.matchInfo AS info, DATE_FORMAT(LM.matchDate, '%d/%m/%y') AS date, divisionName, cupName, round FROM core_matches LM JOIN leaguestats_teams ht ON (ht.teamID = LM.matchHomeID ) JOIN leaguestats_teams at ON (at.teamID = LM.matchAwayID) LEFT JOIN leaguestats_divisions ON (LM.matchDivisionID = divisionID) LEFT JOIN leaguestats_cups ON (LM.matchCupID = cupID) LEFT JOIN leaguestats_cupdetails ON (LM.matchDetails = id) WHERE (ht.teamID = '$ID_TEAM' OR at.teamID = '$ID_TEAM') AND LM.matchSeasonID = '4' ORDER BY LM.matchDate, hometeam

I have tried that, I think its nearly there. SELECT ht.teamName AS hometeam, at.teamName AS awayteam, LM.matchHomeGoals AS goals_home, LM.matchAwayGoals AS goals_away, DATE_FORMAT(LM.matchDate, '%d/%m/%y') AS date, D.divisionName, C.CupName, R.round FROM core_matches LM JOIN leaguestats_teams ht ON (ht.teamID = LM.matchHomeID ) JOIN leaguestats_teams at ON (at.teamID = LM.matchAwayID) LEFT JOIN leaguestats_divisions D ON (D.divisionID = LM.matchDivisionID) LEFT JOIN leaguestats_cups C ON (C.cupID = LM.matchCupID) JOIN leaguestats_cupdetails r ON (r.id=LM.matchDetails) WHERE (ht.teamID = '1' OR at.teamID = '1') AND LM.matchSeasonID = '4' ORDER BY LM.matchDate, hometeam Give me results of matches that have cups with the Division field as null, but leaves out matches in the divisions

I have added JOIN's and now I am able to get the Division or Cup Name but not both at the same time. This will give me the Division Name SELECT ht.teamName AS hometeam, at.teamName AS awayteam, LM.matchHomeGoals AS goals_home, LM.matchAwayGoals AS goals_away, D.DivisionName, DATE_FORMAT(LM.matchDate, '%d/%m/%y') AS date FROM core_matches LM JOIN leaguestats_teams ht ON ht.teamID = LM.matchHomeID JOIN leaguestats_teams at ON at.teamID = LM.matchAwayID JOIN leaguestats_divisions D ON D.divisionID = LM.matchdivisionID WHERE (ht.teamID = '1' OR at.teamID = '1') AND LM.matchSeasonID = '4' ORDER BY LM.matchDate, hometeam This will give me the name of the Cups SELECT ht.teamName AS hometeam, at.teamName AS awayteam, LM.matchHomeGoals AS goals_home, LM.matchAwayGoals AS goals_away, C.CupName, DATE_FORMAT(LM.matchDate, '%d/%m/%y') AS date FROM core_matches LM JOIN leaguestats_teams ht ON ht.teamID = LM.matchHomeID JOIN leaguestats_teams at ON at.teamID = LM.matchAwayID JOIN leaguestats_cups C ON C.cupID = LM.matchCupID WHERE (ht.teamID = '1' OR at.teamID = '1') AND LM.matchSeasonID = '4' ORDER BY LM.matchDate, hometeam But if I add the Cup and Division in the same query I don't get any results returned. C.cupName D.divisionName JOIN leaguestats_cups C ON C.cupID = LM.matchCupID JOIN leaguestats_divisions D ON D.divisionID = LM.matchdivisionID

Say I want to list matches for a team called Newington YC At the moment I get something like the following Date Hometeam Away team Score 11/08/07 Newington YC Drumaness Mills 3 - 2 14/08/07 East Belfast Newington YC 0 - 4 18/08/07 Newington YC Killyleagh YC 2 - 1 21/08/07 Newington YC Wellington Rec P - P 25/08/07 Newington YC Sirocco Works - 28/08/07 Cliftonville Newington YC - What I would like to get is the following Date Opponents Score Competition 11/08/07 Drumaness Mills 3 - 2 Premier Division 14/08/07 East Belfast 0 - 4 Premier Division 18/08/07 Killyleagh YC 2 - 1 Premier Division 21/08/07 Wellington Rec P - P Premier Division 25/08/07 Sirocco Works Steel & Sons Cup 28/08/07 Cliftonville Senior Shield Within the core_matches table in each record the competition is represented by either a divisionID or cupID.

mysql version 5.2.1 I have a football/soccer statistics site that I and trying to include some individual team matches stats. What I am trying to get from the query is the dates, opponents, Division Name/Cup Name, match score for a particular team in a given season. My core_matches tables has the following structure CREATE TABLE `core_matches` ( `matchID` int(10) unsigned NOT NULL auto_increment, `matchDivisionID` int(10) unsigned NOT NULL default '0', `matchCupID` int(11) NOT NULL default '0', `matchSeasonID` int(10) unsigned NOT NULL default '0', `matchDate` date NOT NULL default '0000-00-00', `matchHomeID` smallint(4) unsigned NOT NULL default '0', `matchAwayID` smallint(4) unsigned NOT NULL default '0', `matchHomeWin` smallint(4) NOT NULL default '0', `matchHomeLoss` smallint(4) NOT NULL default '0', `matchAwayWin` smallint(4) NOT NULL default '0', `matchAwayLoss` smallint(4) NOT NULL default '0', `matchHomeDraw` smallint(4) NOT NULL default '0', `matchAwayDraw` smallint(4) NOT NULL default '0', `matchHomeGoals` tinyint(2) default '0', `matchAwayGoals` tinyint(2) default '0', `matchDetails` int(11) NOT NULL default '0', `matchInfo` char(1) collate utf8_unicode_ci NOT NULL default '', `matchReport` text collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`matchID`), KEY `matchDivisionID` (`matchDivisionID`), KEY `matchSeasonID` (`matchSeasonID`), KEY `matchHomeID` (`matchHomeID`), KEY `matchAwayID` (`matchAwayID`), KEY `matchHomeWin` (`matchHomeWin`), KEY `matchHomeLoss` (`matchHomeLoss`), KEY `matchAwayWin` (`matchAwayWin`), KEY `matchAwayLoss` (`matchAwayLoss`), KEY `matchHomeDraw` (`matchHomeDraw`), KEY `matchAwayDraw` (`matchAwayDraw`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; The divisions Table CREATE TABLE `leaguestats_divisions` ( `divisionID` int(10) unsigned NOT NULL auto_increment, `divisionName` varchar(64) collate utf8_unicode_ci NOT NULL default '', `divisionLine` varchar(32) collate utf8_unicode_ci NOT NULL default '', `last_updated` datetime NOT NULL default '0000-00-00 00:00:00', `divisionLeagueID` int(2) unsigned NOT NULL default '0', `information` text collate utf8_unicode_ci NOT NULL, `news` text collate utf8_unicode_ci NOT NULL, `reports` text collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`divisionID`), KEY `divisionLeagueID` (`divisionLeagueID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; The Cups Table CREATE TABLE `leaguestats_cups` ( `cupID` int(10) unsigned NOT NULL auto_increment, `cupName` varchar(64) collate utf8_unicode_ci NOT NULL default '', `cupType` varchar(10) collate utf8_unicode_ci NOT NULL default '', `cupLine` varchar(32) collate utf8_unicode_ci NOT NULL default '1', `last_updated` datetime NOT NULL default '0000-00-00 00:00:00', `cupLeagueID` int(2) unsigned NOT NULL default '0', `news` text collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`cupID`), KEY `cupLeagueID` (`cupLeagueID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; My current effort displays the date, both the team and opponent and score. SELECT ht.teamName AS hometeam, at.teamName AS awayteam, LM.matchHomeGoals AS goals_home, LM.matchAwayGoals AS goals_away, LM.matchInfo AS info, LM.matchReport AS report, DATE_FORMAT(LM.matchDate, '%d/%m/%y') AS date FROM core_matches LM, leaguestats_teams ht, leaguestats_teams at WHERE ht.teamID = LM.matchHomeID AND at.teamID = LM.matchAwayID AND (ht.teamID = '$ID_TEAM' OR at.teamID = '$ID_TEAM') AND LM.matchSeasonID = {$seasonID} ORDER BY LM.matchDate, hometeam This shows me 5 matches 3 in the division and 3 in the cup. Hopefully I have explained this ok and given the relevant information, if I have went overboard or left something out please forgive my ignorance. Thanks