14.e.    Get a list of all the players (ordered by Player_Name), who have played against Dallas in games that Dallas lost ON THE ROAD. Do NOT duplicate player names.

 

SELECT DISTINCT playername, teamname

FROM game, team, employs, player

WHERE visitingteam = '1234'

AND vtscore < htscore

AND hometeam = team.teamID

AND team.teamID = employs.teamID

AND employs.playerID = player.playerID

AND gamedate >= startdate

AND (gamedate <= enddate OR enddate IS NULL)

ORDER BY teamname, playername;

 

This yields the output:

 

PLAYERNAME        TEAMNAME

-------------------------     ------------

Hasek, D.                   Buffalo

Horton, T.                  Buffalo

Martin, R.                  Buffalo

Schoenfeld, J.           Buffalo