実行したい事
現状
実行コード
name(国名の方)と、languageが一致しません。
第一言語を表示とのこと、is_officialがTで、percentageカラムが高いものが第一言語だと思うのですが、表示できないです。
現コードに,percentageカラムが高いものが第一言語なのですが、サブクエリで表示できるのでしょうか?
分かる方居ましたらお願いします。
sql
1SELECT 2`celebrities`.`name`, `countries`.`name`, `countrylanguages`.`language` 3FROM celebrities 4INNER JOIN countries 5ON celebrities.country_code = countries.code INNER JOIN countrylanguages 6ON celebrities.country_code = countrylanguages.country_code 7AND countrylanguages.is_official = TRUE
celebrities テーブル
sql
1INSERT INTO `celebrities` (`id`, `name`, `country_code`, `birth`, `age`, `occupation`) VALUES 2(1, 'Emma Charlotte Duerre Watson', 'FRA', '1990-04-15 00:00:00', 31, 'actor'), 3(2, 'Johnny Depp', 'USA', '1963-06-09 00:00:00', 58, 'actor'), 4(3, 'Jim Carrey', 'USA', '1962-01-17 00:00:00', 59, 'actor'), 5(4, 'Daniel Radcliffe', 'GBR', '1989-07-23 00:00:00', 32, 'actor'), 6(5, 'Morgan Freeman', 'USA', '1937-06-01 00:00:00', 84, 'actor'), 7(6, 'Hugh Jackman', 'AUS', '1968-10-12 00:00:00', 53, 'actor'), 8(7, 'Natalie Portman', '', '1981-06-09 00:00:00', 40, 'actor'), 9(8, 'Pierce Brosnan', 'FRA', '1960-10-15 00:00:00', 61, 'actor'), 10(9, 'Sean Connery', 'PER', '1980-05-05 00:00:00', 41, 'singer'), 11(10, 'Dwayne Johnson', 'USA', '1978-12-15 00:00:00', 43, 'fighter'), 12(11, 'Jackie Chan', 'USA', '1992-04-15 00:00:00', 29, 'fighter'), 13(12, 'Adam Sandler', 'FRA', '1976-02-05 00:00:00', 45, 'singer'), 14(13, 'Scarlett Johansson', 'AUT', '1966-01-10 00:00:00', 55, 'model'), 15(14, 'Heath Ledger', 'USA', '1971-10-15 00:00:00', 50, 'actor'), 16(15, 'Edward Norton', 'GBR', '1988-03-12 00:00:00', 33, 'actor'), 17(16, 'Keira Knightley', 'GBR', '1980-11-10 00:00:00', 41, 'model'), 18(17, 'Bradley Cooper', 'USA', '1979-08-01 00:00:00', 42, 'fighter'), 19(18, 'Will Ferrell', 'USA', '1966-01-15 00:00:00', 53, 'comedian'), 20(19, 'Julia Roberts', 'GRC', '1964-09-01 00:00:00', 55, 'actor'), 21(20, 'Daniel Craig', 'USA', '1977-12-09 00:00:00', 44, 'actor'), 22(21, 'Ian McKellen', '', '1998-04-15 00:00:00', 23, 'singer'), 23(22, 'Samuel L. Jackson', 'USA', '1960-04-09 00:00:00', 61, 'actor'), 24(23, 'Ben Stiller', 'BRA', '1964-10-15 00:00:00', 57, 'actor'), 25(24, 'Tommy Lee Jones', '', '1970-09-05 00:00:00', 51, 'comedian'), 26(25, 'Antonio Banderas', 'USA', '1958-10-05 00:00:00', 63, 'actor'), 27(26, 'Denzel Washington', 'USA', '1990-10-25 00:00:00', 31, 'model'), 28(27, 'Steve Carell', 'USA', '1991-02-14 00:00:00', 30, 'actor'), 29(28, 'Shia LaBeouf', 'CHL', '1991-10-05 00:00:00', 30, 'actor'), 30(29, 'Megan Fox', 'GRC', '1992-09-23 00:00:00', 29, 'actor'), 31(30, 'James Franco', 'USA', '1955-04-15 00:00:00', 66, 'actor'), 32(31, 'Mel Gibson', 'COL', '1967-03-08 00:00:00', 54, 'singer'), 33(32, 'Vin Diesel', 'USA', '1982-11-15 00:00:00', 39, 'singer'), 34(33, 'Tim Allen', 'ISL', '1962-02-10 00:00:00', 59, 'actor'), 35(34, 'Kevin Spacey', 'USA', '1990-04-15 00:00:00', 31, 'actor'), 36(35, 'Jason Biggs', 'USA', '1979-03-25 00:00:00', 42, 'actor'), 37(36, 'Seann William Scott', 'USA', '1957-02-22 00:00:00', 64, 'actor'), 38(37, 'Jean-Claude Van Damme', 'USA', '1997-01-10 00:00:00', 24, 'model'), 39(38, 'Zach Galifianakis', 'USA', '1969-04-22 00:00:00', 52, 'actor'), 40(39, 'Owen Wilson', 'USA', '1988-06-05 00:00:00', 31, 'actor'), 41(40, 'Christian Bale', 'USA', '1977-12-01 00:00:00', 44, 'actor'), 42(41, 'Peter Jackson', 'USA', '1994-10-25 00:00:00', 27, 'model'), 43(42, 'Sandra Bullock', 'CUA', '1975-07-19 00:00:00', 46, 'actor'), 44(43, 'Drew Barrymore', 'USA', '1993-06-05 00:00:00', 28, 'singer'), 45(44, 'Macaulay Culkin', 'ESP', '1981-07-22 00:00:00', 40, 'comedian'), 46(45, 'Bill Murray', 'USA', '1988-03-09 00:00:00', 33, 'actor'), 47(46, 'Sigourney Weaver', 'FIN', '1954-12-10 00:00:00', 67, 'actor'), 48(47, 'Jake Gyllenhaal', '', '1990-05-05 00:00:00', 31, 'model'), 49(48, 'Jason Statham', 'USA', '1986-06-08 00:00:00', 35, 'actor');
countries テーブル
sql
1 2INSERT INTO `countries` (`code`, `name`, `continent`, `region`, `surface_area`, `indep_year`, `population`, `life_expectancy`, `gnp`, `gnp_old`, `local_name`, `government_form`, `hea_of_state`, `capital`, `code2`) VALUES 3('ABW', 'Aruba', 'North America', 'Caribbean', '193.00', NULL, 103000, '78.4', '828.00', '793.00', 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', 129, 'AW'), 4('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '652090.00', 1919, 22720000, '45.9', '5976.00', NULL, 'Afganistan/Afqanestan', 'Islamic Emirate', 'Mohammad Omar', 1, 'AF'), 5('AGO', 'Angola', 'Africa', 'Central Africa', '1246700.00', 1975, 12878000, '38.3', '6648.00', '7984.00', 'Angola', 'Republic', 'José Eduardo dos Santos', 56, 'AO'), 6('AIA', 'Anguilla', 'North America', 'Caribbean', '96.00', NULL, 8000, '76.1', '63.20', NULL, 'Anguilla', 'Dependent Territory of the UK', 'Elisabeth II', 62, 'AI'), 7('ALB', 'Albania', 'Europe', 'Southern Europe', '28748.00', 1912, 3401200, '71.6', '3205.00', '2500.00', 'Shqipëria', 'Republic', 'Rexhep Mejdani', 34, 'AL'), 8('AND', 'Andorra', 'Europe', 'Southern Europe', '468.00', 1278, 78000, '83.5', '1630.00', NULL, 'Andorra', 'Parliamentary Coprincipality', '', 55, 'AD'), 9('ANT', 'Netherlands Antilles', 'North America', 'Caribbean', '800.00', NULL, 217000, '74.7', '1941.00', NULL, 'Nederlandse Antillen', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', 33, 'AN'), 10('ARE', 'United Arab Emirates', 'Asia', 'Middle East', '83600.00', 1971, 2441000, '74.1', '37966.00', '36846.00', 'Al-Imarat al-´Arabiya al-Muttahida', 'Emirate Federation', 'Zayid bin Sultan al-Nahayan', 65, 'AE'), 11('ARG', 'Argentina', 'South America', 'South America', '2780400.00', 1816, 37032000, '75.1', '340238.00', '323310.00', 'Argentina', 'Federal Republic', 'Fernando de la Rúa', 69, 'AR'), 12('ARM', 'Armenia', 'Asia', 'Middle East', '29800.00', 1991, 3520000, '66.4', '1813.00', '1627.00', 'Hajastan', 'Republic', 'Robert Kotšarjan', 126, 'AM'), 13('ASM', 'American Samoa', 'Oceania', 'Polynesia', '199.00', NULL, 68000, '75.1', '334.00', NULL, 'Amerika Samoa', 'US Territory', 'George W. Bush', 54, 'AS'), 14('ATA', 'Antarctica', 'Antarctica', 'Antarctica', '13120000.00', NULL, 0, NULL, '0.00', NULL, '–', 'Co-administrated', '', NULL, 'AQ'), 15('ATF', 'French Southern territories', 'Antarctica', 'Antarctica', '7780.00', NULL, 0, NULL, '0.00', NULL, 'Terres australes françaises', 'Nonmetropolitan Territory of France', 'Jacques Chirac', NULL, 'TF'), 16('ATG', 'Antigua and Barbuda', 'North America', 'Caribbean', '442.00', 1981, 68000, '70.5', '612.00', '584.00', 'Antigua and Barbuda', 'Constitutional Monarchy', 'Elisabeth II', 63, 'AG'), 17('AUS', 'Australia', 'Oceania', 'Australia and New Zealand', '7741220.00', 1901, 18886000, '79.8', '351182.00', '392911.00', 'Australia', 'Constitutional Monarchy, Federation', 'Elisabeth II', 135, 'AU'), 18('AUT', 'Austria', 'Europe', 'Western Europe', '83859.00', 1918, 8091800, '77.7', '211860.00', '206025.00', 'Österreich', 'Federal Republic', 'Thomas Klestil', 1523, 'AT'), 19('AZE', 'Azerbaijan', 'Asia', 'Middle East', '86600.00', 1991, 7734000, '62.9', '4127.00', '4100.00', 'Azärbaycan', 'Federal Republic', 'Heydär Äliyev', 144, 'AZ'), 20('BDI', 'Burundi', 'Africa', 'Eastern Africa', '27834.00', 1962, 6695000, '46.2', '903.00', '982.00', 'Burundi/Uburundi', 'Republic', 'Pierre Buyoya', 552, 'BI'), 21('BEL', 'Belgium', 'Europe', 'Western Europe', '30518.00', 1830, 10239000, '77.8', '249704.00', '243948.00', 'België/Belgique', 'Constitutional Monarchy, Federation', 'Albert II', 179, 'BE'), 22('BEN', 'Benin', 'Africa', 'Western Africa', '112622.00', 1960, 6097000, '50.2', '2357.00', '2141.00', 'Bénin', 'Republic', 'Mathieu Kérékou', 187, 'BJ'), 23('BFA', 'Burkina Faso', 'Africa', 'Western Africa', '274000.00', 1960, 11937000, '46.7', '2425.00', '2201.00', 'Burkina Faso', 'Republic', 'Blaise Compaoré', 549, 'BF'), 24('BGD', 'Bangladesh', 'Asia', 'Southern and Central Asia', '143998.00', 1971, 129155000, '60.2', '32852.00', '31966.00', 'Bangladesh', 'Republic', 'Shahabuddin Ahmad', 150, 'BD'), 25('BGR', 'Bulgaria', 'Europe', 'Eastern Europe', '110994.00', 1908, 8190900, '70.9', '12178.00', '10169.00', 26 27 28省略〜
countrylanguages テーブル
sql
1INSERT INTO `countrylanguages` (`country_code`, `language`, `is_official`, `percentage`) VALUES 2('GEO', 'Abhyasi', 'F', '1.7'), 3('UGA', 'Acholi', 'F', '4.4'), 4('BEN', 'Adja', 'F', '11.1'), 5('DJI', 'Afar', 'F', '34.8'), 6('ERI', 'Afar', 'F', '4.3'), 7('NAM', 'Afrikaans', 'F', '9.5'), 8('ZAF', 'Afrikaans', 'T', '14.3'), 9('BOL', 'Aimará', 'T', '3.2'), 10('CHL', 'Aimará', 'F', '0.5'), 11('PER', 'Aimará', 'T', '2.3'), 12('JPN', 'Ainu', 'F', '0.0'), 13('BEN', 'Aizo', 'F', '8.7'), 14('CIV', 'Akan', 'F', '30.0'), 15('GHA', 'Akan', 'F', '52.4'), 16('ALB', 'Albaniana', 'T', '97.9'), 17('ITA', 'Albaniana', 'F', '0.2'), 18('MKD', 'Albaniana', 'F', '22.9'), 19('YUG', 'Albaniana', 'F', '16.5'), 20('AGO', 'Ambo', 'F', '2.4'), 21('ETH', 'Amhara', 'F', '30.0'), 22('TWN', 'Ami', 'F', '0.6'), 23('TGO', 'Ane', 'F', '5.7'), 24('ARE', 'Arabic', 'T', '42.0'), 25('AUS', 'Arabic', 'F', '1.0'), 26('BEL', 'Arabic', 'F', '1.6'), 27('BHR', 'Arabic', 'T', '67.7'), 28('DJI', 'Arabic', 'T', '10.6'), 29('DNK', 'Arabic', 'F', '0.7'), 30('DZA', 'Arabic', 'T', '86.0'), 31('EGY', 'Arabic', 'T', '98.8'), 32('ESH', 'Arabic', 'T', '100.0'), 33('FRA', 'Arabic', 'F', '2.5'), 34('GIB', 'Arabic', 'F', '7.4'), 35('IRN', 'Arabic', 'F', '2.2'), 36('IRQ', 'Arabic', 'T', '77.2'), 37('ISR', 'Arabic', 'T', '18.0'), 38('JOR', 'Arabic', 'T', '97.9'), 39('KWT', 'Arabic', 'T', '78.1'), 40('LBN', 'Arabic', 'T', '93.0'), 41('LBY', 'Arabic', 'T', '96.0'), 42('MAR', 'Arabic', 'T', '65.0'), 43('NLD', 'Arabic', 'F', '0.9'), 44 45省略〜