Mysql query Syntax help required

[stb74]

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

[fenway]

What do you want it to show?

[stb74]

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.

 

 

 

 

 

 

[stb74]

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

 

 

[Illusion]

so it should be left join or a right join.

[stb74]

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

[stb74]

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

[stb74]

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

 

 

[fenway]

Try this (untested):

 

SELECT t.teamName AS opponents
FROM core_matches LM
JOIN leaguestats_teams t ON ( t.teamID = IF( LM.matchHomeID = '1', LM.matchAwayID, LM.matchHomeID ) )
WHERE ( LM.matchHomeID = '1' OR LM.matchAwayID = '1' )
AND LM.matchSeasonID = '4'
ORDER BY LM.matchDate, opponents

[stb74]

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

[stb74]

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

 

Cheers

[fenway]

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.

 

Sure...

SELECT t.teamName AS opponents, IF( LM.matchHomeID = '1', 'home', 'away' ) AS playedWhere
FROM core_matches LM
JOIN leaguestats_teams t ON ( t.teamID = IF( LM.matchHomeID = '1', LM.matchAwayID, LM.matchHomeID ) )
WHERE ( LM.matchHomeID = '1' OR LM.matchAwayID = '1' )
AND LM.matchSeasonID = '4'
ORDER BY LM.matchDate, opponents