I need help with this SQL question I have seen answers that work for only two le
ID: 3781990 • Letter: I
Question
I need help with this SQL question I have seen answers that work for only two leagues, but as the question states, the solution has to work for an unknown amount of leagues 4) List all cities that have a team in all leagues. For example, there are currently two leagues (National and American). Although not shown in this instance, New York is home to the Mets in the National league as well as the Yankees in the American league (Chicago also has one in each league, for those of you who are baseball fans). Remember that your query must work over all instances of this schema, even if there are more than two leagues in the instance.
Databases: Print all teamIDs where the team played against th Databases Code:
CREATE TABLE `teams` ( `teamID` tinyint(4) default NULL, `teamName` varchar(50) default NULL, `home` varchar(50) default NULL, `leagueName` varchar(50) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('1','Phillies','Philadelphia','National'); INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('2','Braves','Atlanta','National'); INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('3','Yankees','New York','American'); INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('4','Twins','Minnesota','American'); INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('5','Rangers','Texas','American'); INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('6','Cubs','Chicago','National');
Explanation / Answer
select home, count(leagueName )from teams group by leagueName
having count(leagueName) >= 2
above querry will print the output as all cities that have a team in all leagues.