CODEWITHSUNDEEP
mysqldatabase
GigaByte
GigaByte

Reputation: 700

Sql Query to Find the teams who have played all tournaments?

My schema is:

  1. country(countryName**(Primary Key)**)
  2. tournament(year,date) having composite primary key (year,date)
  3. team(ID,Year,Country) having composite PK (ID,year,Country)

Data in Tournament table is:

CREATE TABLE `tournament` (
  `Year` date NOT NULL,
  `Country` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tournament` (`Year`, `Country`) VALUES
('2015-12-17', 'Canada'),
('2015-12-17', 'USA'),
('2016-12-17', 'Canada'),
('2016-12-17', 'USA'),
('2017-12-17', 'UK'),
('2017-12-17', 'USA'),
('2018-12-17', 'China'),
('2018-12-17', 'USA'),
('2019-12-17', 'Australia'),
('2019-12-17', 'USA');

ALTER TABLE `tournament`
  ADD PRIMARY KEY (`Year`,`Country`),
  ADD KEY `country_tournament_FK` (`Country`);

ALTER TABLE `tournament`
  ADD CONSTRAINT `country_tournament_FK` FOREIGN KEY (`Country`) REFERENCES `country` (`Name`);
COMMIT;

Data in Team's Table:

CREATE TABLE `team` (
  `Year` date NOT NULL,
  `ID` int(50) NOT NULL,
  `Country` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `team` (`Year`, `ID`, `Country`) VALUES
('2015-12-17', 6, 'Australia'),
('2016-12-17', 2, 'Canada'),
('2019-12-17', 1, 'China'),
('2016-12-17', 1, 'UK'),
('2017-12-17', 2, 'UK'),
('2015-12-17', 5, 'USA'),
('2016-12-17', 5, 'USA'),
('2017-12-17', 5, 'USA'),
('2018-12-17', 5, 'USA'),
('2019-12-17', 5, 'USA');

ALTER TABLE `team`
  ADD PRIMARY KEY (`Year`,`ID`),
  ADD KEY `team_country_fk` (`Country`);

ALTER TABLE `team`
  ADD CONSTRAINT `team_country_fk` FOREIGN KEY (`Country`) REFERENCES `country` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `team_tournament_fk_final` FOREIGN KEY (`Year`) REFERENCES `tournament` (`Year`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

I want to select those teams who have played all tournaments(mean in all years 2015 to 2019)? Stuck on this query, i'm a student looking for a solution to this query so please let me know either its possible or not any good clue?

Upvotes: 0

Views: 1069

Answers (2)

kkica
kkica

Reputation: 4104

Something like this if id is an autonumber and you have only 1 team per country. 

select country    
from team
group by country
having Count(*)=(select count(distinct year) from tournament)

If id is an id of a team and you have another table "team" where id is a foreign key to this table, you have something like this

select team.id
from team
group by team.id
having Count(*)=(select count(*) from tournament)

Upvotes: 1

valegians
valegians

Reputation: 940

Your answer will be a simple

SELECT X FROM Y WHERE CONDITION_1 AND CONDITION_2

In your case, you would have something like:

SELECT Country FROM team WHERE Year >= 2015-12-17 AND Year <= 2019-12-17

The query means:

Select rows from the 'Country' column located in the 'team' table that meet the condition of 'Year' being a value between 2015-12-17 and 2019-12-17.

There are many results on google if you look for "SQL Date Range" or similar stuff. There are also other ways to solve your problem, for example using the BETWEEN operation.

Upvotes: 0

Related Questions