Hello,I have a small problem with my sql query that uses a right join for my ufc database as attached, however, i have got a feedback that the query is not correct and i would like this to be fixed.I have a table fighters_has_fights which is basically the many to many relationship or join table between fighters table and fights. I want you to write a proper right join query based on my database as i have looked how its done but couldnt find anything relevant. Please help!(SELECT Fighters.First_Name,Fighters.Last_Name,Fighters.Nickname,Fights.Wins,Fights.Losses,Fights.Total_Fights FROM Fighters right JOIN Fightson Fighters.Fighters_id = Fights.Total_Fights;)
Ufc.sql -- MySQL dump 10.13 Distrib 8.0.21, for macos10.15 (x86_64) -- -- Host: localhost Database: UFC -- ------------------------------------------------------ -- Server version8.0.23 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `Champion` -- DROP TABLE IF EXISTS `Champion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Champion` ( `Champion_id` int NOT NULL AUTO_INCREMENT, `Date` varchar(45) DEFAULT NULL, `Defence_Title` int DEFAULT NULL, `Current_champ` varchar(45) DEFAULT NULL, PRIMARY KEY (`Champion_id`) ) ENGINE=InnoDB AUTO_INCREMENT=9485 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `Champion` -- LOCK TABLES `Champion` WRITE; /*!40000 ALTER TABLE `Champion` DISABLE KEYS */; INSERT INTO `Champion` VALUES (1,'2020-08-12',NULL,'Champ'),(2,NULL,NULL,NULL),(3,NULL,NULL,NULL),(4,NULL,NULL,NULL),(5,NULL,NULL,NULL),(6,NULL,NULL,NULL),(7,NULL,NULL,NULL),(8,NULL,NULL,NULL),(9,NULL,NULL,NULL),(10,NULL,NULL,NULL),(11,NULL,NULL,NULL),(12,NULL,NULL,NULL),(13,NULL,NULL,NULL),(14,NULL,NULL,NULL),(15,NULL,NULL,NULL),(16,NULL,NULL,NULL),(17,NULL,NULL,NULL),(18,NULL,NULL,NULL),(19,NULL,NULL,NULL),(20,NULL,NULL,NULL),(21,NULL,NULL,NULL),(22,NULL,NULL,NULL),(23,NULL,NULL,NULL),(24,NULL,NULL,NULL),(25,NULL,NULL,NULL),(26,NULL,NULL,NULL),(27,NULL,NULL,NULL),(28,NULL,NULL,NULL),(29,NULL,NULL,NULL),(30,NULL,NULL,NULL),(31,NULL,NULL,NULL),(32,NULL,NULL,NULL),(33,NULL,NULL,NULL),(34,NULL,NULL,NULL),(35,NULL,NULL,NULL),(36,NULL,NULL,NULL),(37,NULL,NULL,NULL),(38,NULL,NULL,NULL),(39,NULL,NULL,NULL),(40,NULL,NULL,NULL),(41,NULL,NULL,NULL); /*!40000 ALTER TABLE `Champion` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `Favorite_Fight_Style` -- DROP TABLE IF EXISTS `Favorite_Fight_Style`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Favorite_Fight_Style` ( `Fight_Style_Id` varchar(55) NOT NULL, `Name` varchar(45) NOT NULL DEFAULT '', `Fighters_Fighters_id` int NOT NULL, PRIMARY KEY (`Fight_Style_Id`), KEY `fk_Favorite_Fight_Style_Fighters1_idx1` (`Fighters_Fighters_id`), CONSTRAINT `fk_Favorite_Fight_Style_Fighters1` FOREIGN KEY (`Fighters_Fighters_id`) REFERENCES `Fighters` (`Fighters_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `Favorite_Fight_Style` -- LOCK TABLES `Favorite_Fight_Style` WRITE; /*!40000 ALTER TABLE `Favorite_Fight_Style` DISABLE KEYS */; INSERT INTO `Favorite_Fight_Style` VALUES ('Bjj1','Brasilian jiu-jitsu',3),('Bjj2','Brasilian Jiu Jitsu',28),('Bjjju1','Brasilian jiu jitsu',12),('BjjKicktk1','Brasiliansk jiu-jitsu/Kickboxning/Taekwondo',19),('Box3','Boxing',10),('Box4','Boxing',16),('Box5','Boxing',20),('Box6','Boxing',24),('Box7','Boxing',27),('Box8','Boxing',31),('Box9','Boxing',33),('Boxbjj1','Boxing/Brazilian jiu-jitsu',9),('Boxjj1','Boxing/Jiu Jitsu',22),('BoxJj2','Boxing/Jiu Jitsu',25),('Boxju1','Boxing/Judo',15),('Boxmu01','Boxing/muay Thai',34),('Boxwres2','Boxing/Wrestling',21),('Jj01','Jiu Jitsu',18),('Jjbox1','Jiu Jitsu,Boxing',13),('Jjbox2','Jiu Jitsu/Boxing',32),('Jud','Judo',38),('Mu01','Kungfu',1),('MuBox1','Boxing/Brazilian jiu-jitsu',8),('smb1','Sambo',11),('Tboxtk1','Thai boxing/ Taekwondo',36),('Tk1','Taekwando',37),('Tkw21','Taekwando',40),('Wr2','Wrestling',6),('Wrbjj1','Boxing/Wrestling/Brasilian Jiu-Jitsu',7),('Wres2','Wrestling',17),('Wres3','Wresting',23),('Wres4','Wrestling',26),('Wres5','Wrestling',29),('Wres6','Wrestling',30),('Wrs1','Wrestling',5),('Wrsbox1','Wrestling/Boxing',14); /*!40000 ALTER TABLE `Favorite_Fight_Style` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `Fighters` -- DROP TABLE IF EXISTS `Fighters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Fighters` ( `Fighters_id` int NOT NULL AUTO_INCREMENT, `First_Name` varchar(45) NOT NULL, `Last_Name` varchar(45) NOT NULL, `Nickname` varchar(45) NOT NULL, `State` varchar(45) NOT NULL, `Country` varchar(45) NOT NULL, `Weight_Class_Weight_Id` varchar(45) NOT NULL, `Champion_Champion_id` int DEFAULT NULL, PRIMARY KEY (`Fighters_id`), KEY `fk_Fighters_Weight_Class1_idx` (`Weight_Class_Weight_Id`), KEY `fk_Fighters_Champion1_idx` (`Champion_Champion_id`), CONSTRAINT `fk_Fighters_Champion1` FOREIGN KEY (`Champion_Champion_id`) REFERENCES `Champion` (`Champion_id`), CONSTRAINT `fk_Fighters_Weight_Class1` FOREIGN KEY (`Weight_Class_Weight_Id`) REFERENCES `Weight_Class` (`Weight_Id`) ) ENGINE=InnoDB AUTO_INCREMENT=1153 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `Fighters` -- LOCK TABLES `Fighters` WRITE; /*!40000 ALTER TABLE `Fighters` DISABLE KEYS */; INSERT INTO `Fighters` VALUES (1,'Israel','Adesanya','Last Stylebender','Lagos','Nigeria','1',1),(2,'Tim','Boetsch','The Barbarian','Maine','USA','2',2),(3,'Brad','Tavares','Haiwain Dream','Kailua','Hawaii','3',3),(4,'Chris','Weidman','The All-American','New York','USA','4',4),(5,'Ronaldo','Souza','Jacaré','Espirito Santo','Brazil','5',5),(6,'Trevor','Smith','Hot Sauce','Washington','USA','6',6),(7,'Derek','Brunson','The Human Blanket','North Carolina','USA','7',7),(8,'Robert','Whittaker','The Reaper','Auckland','New Zealand','8',8),(9,'Uriah','Hall','Prime','Time Spanish Town','Jamaica','9',9),(10,'Kelvin','Gastelum','Krispy Kreme','California','USA','10',10),(11,'Omari','Akhmedov','Wolverine','Kizjar','Russia','11',11),(12,'Atonio','Carlos Junior','Cara de sapato','Paraiba','Brazil','12',12),(13,'Kelvin','Holland','Trailblazer','Texas','USA','13',13),(14,'Jared','Cannonier','The Killer Gorilla','Texas','USA','14',14),(15,'Jack','Hermansson','The Joker','Udevalla','Sweden','15',15),(16,'Bartosz','Fabinski','The Butcher','Warsaw','Poland','16',16),(17,'Daren','Till','The Gorilla','Liverpool','England','17',17),(18,'Marvin','Vettori','The Italian Dream','Trento','Italy','18',18),(19,'Alessio','Di Chirico','Manzo','Rome','Italy','19',19),(20,'Gerald','Meerschaert','GM3','Wisconsin','USA','20',20),(21,'Paulo','Costa','The Eraser','Minas Gerais','Brazil','21',21),(22,'Trevin','Giles','The problem','Texas','USA','22',22),(23,'Eryk','Anders','Ya Boi','Alabama','USA','23',23),(24,'Karl','Roberson','Baby K','New Jersey','USA','24',24),(25,'Ian','Henisch','The Hurricane','Colorado','USA','25',25),(26,'Anthony','Hernandez','Fluffy','California','USA','26',26),(27,'Mark-Andre','Barriault','Power Bar','Quebec','Canada','27',27),(28,'Brendan','Allen','All in','South Carolina','USA','28',28),(29,'Andre','Muniz','Sergipano','Minas Gerais','Brazil','29',29),(30,'Khamzat','Chimaev','Borz','Malmö','Sweden','30',30),(31,'KB','Bhullar','The Bengal','Alberta','Canada','31',31),(32,'Joaqin','Buckley','New Mansa','Missouri','USA','32',32),(33,'Tafon','Nchukwi','Camaron Express','Bamenda','Cameroon','33',33),(34,'Charlie','Ontiveros','The American Badboy','Texas','USA','34',34),(35,'Phil','Hawes','Megatron','New Jersey','USA','35',35),(36,'Andersson','Silver','Spider','Sao Paulo','Brazil','36',36),(37,'Park','Jun-Young','The Iron Turtle','Seoul','South Korea','37',37),(38,'Impala','Kasanganay','Tshilobo','North Carolina','USA','38',38),(39,'Wellington','Turman','The Prodigy','Curitiba','Brazil','39',39),(40,'Dricus','Du Plessis','StillKnocks','Pretoria','South Africa','40',40),(41,'Jacob','Malkoun','Mamba','Sydney','Australia','41',41); /*!40000 ALTER TABLE `Fighters` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `Fighters_has_Fights` -- DROP TABLE IF EXISTS `Fighters_has_Fights`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Fighters_has_Fights` ( `Fighters_Fighters_id` int NOT NULL, `Fights_Total_Fights` int NOT NULL, PRIMARY KEY (`Fighters_Fighters_id`), KEY `fk_Fighter(s)_has_Fights_Fights1_idx` (`Fights_Total_Fights`), KEY `fk_Fighter(s)_has_Fights_Fighter(s)1_idx` (`Fighters_Fighters_id`), CONSTRAINT `fk_Fighter(s)_has_Fights_Fighter(s)1` FOREIGN KEY (`Fighters_Fighters_id`) REFERENCES `Fighters` (`Fighters_id`), CONSTRAINT `fk_Fighter(s)_has_Fights_Fights1` FOREIGN KEY (`Fights_Total_Fights`) REFERENCES `Fights` (`Total_Fights`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `Fighters_has_Fights` -- LOCK TABLES `Fighters_has_Fights` WRITE; /*!40000 ALTER TABLE `Fighters_has_Fights` DISABLE KEYS */; INSERT INTO `Fighters_has_Fights` VALUES (34,8),(40,10),(41,11),(8,16),(33,17),(16,19),(6,20),(18,21),(2,23),(11,25),(14,26),(4,27),(17,28),(15,29),(10,31),(21,32),(32,33),(5,34),(31,35),(22,36),(25,37),(29,38),(12,39),(24,40),(20,41),(3,42),(23,43),(26,44),(30,45),(7,46),(28,47),(19,48),(27,49),(1,50),(35,51),(36,52),(13,53),(9,54),(37,57),(38,58),(39,60); /*!40000 ALTER TABLE `Fighters_has_Fights` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `Fights` -- DROP TABLE IF EXISTS `Fights`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Fights` ( `Wins` int NOT NULL, `Losses` int NOT NULL, `Fighters_id` int NOT NULL, `Total_Fights` int NOT NULL, PRIMARY KEY (`Total_Fights`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `Fights` -- LOCK TABLES `Fights` WRITE; /*!40000 ALTER TABLE `Fights` DISABLE KEYS */; INSERT INTO `Fights` VALUES (4,4,34,8),(7,3,40,10),(8,3,41,11),(8,8,8,16),(12,6,33,17),(5,14,16,19),(11,9,6,20),(8,13,18,21),(18,5,2,23),(15,10,11,25),(20,6,14,26),(21,6,4,27),(20,8,17,28),(14,15,15,29),(27,4,10,31),(17,15,21,32),(17,16,32,33),(17,17,5,34),(19,16,31,35),(18,18,22,36),(29,8,25,37),(25,13,29,38),(30,9,12,39),(30,10,24,40),(30,11,20,41),(30,12,3,42),(30,13,23,43),(30,44,26,44),(36,9,30,45),(31,15,7,46),(33,14,28,47),(35,13,19,48),(39,10,27,49),(50,0,1,50),(40,11,35,51),(45,8,36,52),(45,8,13,53),(40,54,9,54),(40,10,37,57),(38,20,38,58),(40,20,39,60); /*!40000 ALTER TABLE `Fights` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `Hall_Of_Fame` -- DROP TABLE IF EXISTS `Hall_Of_Fame`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Hall_Of_Fame` ( `Fame_Id` int NOT NULL AUTO_INCREMENT, `First_Name` varchar(45) NOT NULL, `Last_Name` varchar(45) NOT NULL, `DOI` date DEFAULT NULL, `Fighters_Identity` int NOT NULL, PRIMARY KEY (`Fame_Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `Hall_Of_Fame` -- LOCK TABLES `Hall_Of_Fame` WRITE; /*!40000 ALTER TABLE `Hall_Of_Fame` DISABLE KEYS */; /*!40000 ALTER TABLE `Hall_Of_Fame` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `Weight_Class` -- DROP TABLE IF EXISTS `Weight_Class`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Weight_Class` ( `Weight_Id` varchar(45) NOT NULL, `Weight_lbs` int NOT NULL, `Name` varchar(45) NOT NULL, PRIMARY KEY (`Weight_Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `Weight_Class` -- LOCK TABLES `Weight_Class` WRITE; /*!40000 ALTER TABLE `Weight_Class` DISABLE KEYS */; INSERT INTO `Weight_Class` VALUES ('1',184,'Middle Weight'),('10',182,'Middle Weight'),('11',177,'Middle Weight'),('12',182,'Middle Weight'),('13',178,'Middle Weight'),('14',179,'Middle Weight'),('15',177,'Middle Weight'),('16',181,'Middle Weight'),('17',181,'Middle Weight'),('18',183,'Middle Weight'),('19',181,'Middle Weight'),('2',183,'Middle Weight'),('20',185,'Middle Weight'),('21',178,'Middle Weight'),('22',179,'Middle Weight'),('23',185,'Middle Weight'),('24',179,'Middle Weight'),('25',182,'Middle Weight'),('26',181,'Middle Weight'),('27',182,'Middle Weight'),('28',181,'Middle Weight'),('29',178,'Middle Weight'),('3',182,'Middle Weight'),('30',185,'Middle Weight'),('31',179,'Middle Weight'),('32',180,'Middle Weight'),('33',178,'Middle Weight'),('34',181,'Middle Weight'),('35',180,'Middle Weight'),('36',182,'Middle Weight'),('37',182,'Middle Weight'),('38',181,'Middle Weight'),('39',185,'Middle Weight'),('4',183,'Middle Weight'),('40',183,'Middle Weight'),('41',182,'Middle Weight'),('5',181,'Middle Weight'),('6',183,'Middle Weight'),('7',177,'Middle Weight'),('8',183,'Middle Weight'),('9'