CODEWITHSUNDEEP
mysqlsql
Shivam Chaurasia
Shivam Chaurasia

Reputation: 494

Join Returning Incorrect Values

Table Structure of the Database:
Coach table 

+----+-----------+-------+  
| ID |   Name    | Age   |  
+----+-----------+-------+  
|  1 | Aaron     |  39   |  
|  2 | John      |  41   |  
|  3 | Macy      |  44   |  
|  4 | Mitchelle |  37   |  
|  5 | Candice   |  32   |  
+----+-----------+-------+

Trainee table 

+----+---------+-------+  
| ID |  Name   | Age   |  
+----+---------+-------+  
|  6 | Abigail |   9   |  
|  7 | Jim     |  12   |  
|  8 | Jack    |   7   |  
|  9 | Maria   |  14   |  
| 10 | Andy    |   11  |  
+----+---------+-------+

Fee table

+----+----------+------------+--------+  
| ID | Coach_ID | Trainee_ID | Fee    |  
+----+----------+------------+--------+  
| 11 |        1 |          7 | 2400   |  
| 12 |        2 |          6 | 2000   |  
| 13 |        3 |          6 | 2000   |  
| 14 |        4 |          8 | 1243   |  
| 15 |        5 |          8 | 1275   |  
| 16 |        3 |          9 | 9010   |  
| 17 |        2 |          8 | 1900   |  
| 18 |        1 |          7 |  600   |  
| 19 |        2 |         10 | 1010   |  
| 20 |        5 |         10 |   2110 |  
+----+----------+------------+--------+

Required Output 

+-----------+--------------+--------+  
|   Name    | Trainee_name | Fee    |  
+-----------+--------------+--------+  
| Aaron     | Jim          | 2400   |  
| Candice   | Andy         | 2110   |  
| John      | Abigail      | 2000   |  
| Macy      | Maria        | 9010   |  
| Mitchelle | Jack         |  1243  |  
+-----------+--------------+--------+

=====

My QUERY

Select 
 Coach.Name as Name, Trainee.Name as Trainee_name, Max(Fee.Fee) as Fee 
From Fee
 INNER JOIN Trainee
  ON Fee.Trainee_ID = Trainee.ID
 INNER JOIN Coach
  ON Fee.Coach_ID = Coach.ID 
GROUP BY Coach.Name
ORDER BY Coach.Name;

My Output: 

+------------+---------------+--------+  
|   Name     | Trainee_name  | Fee    |  
+------------+---------------+--------+  
| Aaron      | Jim           | 2400   |  
| Candice    | Jack          | 2110   |  
| John       | Abigail       | 2000   |  
| Macy       | Abigail       | 9010   |  
| Mitchelle  | Jack          |  1243  |  
+------------+---------------+--------+

The data corresponding to Candice and Macy are incorrect in trainee_name column.

Dump of sql database: 

CREATE TABLE IF NOT EXISTS `coach` (  
  `ID` int(11) NOT NULL,  
  `Name` text NOT NULL,  
  `Age` int(11) NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  


INSERT INTO `coach` (`ID`, `Name`, `Age`) VALUES  
(1, 'Aaron', 39),  
(2, 'John', 41),  
(3, 'Macy', 44),  
(4, 'Mitchelle', 37),  
(5, 'Candice', 32);  


CREATE TABLE IF NOT EXISTS `fee` (  
  `ID` int(11) NOT NULL,  
  `Coach_ID` int(11) NOT NULL,  
  `Trainee_ID` int(11) NOT NULL,  
  `Fee` int(11) NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  


INSERT INTO `fee` (`ID`, `Coach_ID`, `Trainee_ID`, `Fee`) VALUES  
(11, 1, 7, 2400),  
(12, 2, 6, 2000),  
(13, 3, 6, 2000),  
(14, 4, 8, 1243),  
(15, 5, 8, 1275),  
(16, 3, 9, 9010),  
(17, 2, 8, 1900),  
(18, 1, 7, 600),  
(19, 2, 10, 1010),  
(20, 5, 10, 2110);  

CREATE TABLE IF NOT EXISTS `trainee` (  
  `ID` int(11) NOT NULL,  
  `Name` text NOT NULL,  
  `Age` int(11) NOT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  


INSERT INTO `trainee` (`ID`, `Name`, `Age`) VALUES  
(6, 'Abigail', 9),  
(7, 'Jim', 12),  
(8, 'Jack', 7),  
(9, 'Maria', 14),  
(10, 'Andy', 11);

Upvotes: 1

Views: 78

Answers (1)

kjmerf
kjmerf

Reputation: 4335

Try this:

SELECT sub.name, trainee.name, sub.mx
FROM
(SELECT Fee.Coach_ID, Coach.Name, MAX(fee) AS mx
 FROM Coach
 INNER JOIN Fee ON Coach.ID = Fee.Coach_ID
 GROUP BY Fee.Coach_ID, Coach.Name) sub
INNER JOIN fee ON sub.coach_ID = fee.coach_ID
INNER JOIN trainee ON fee.trainee_ID = trainee.id
WHERE sub.mx = fee.fee
ORDER BY sub.name

You can see the test I ran here: http://sqlfiddle.com/#!9/cdbad/10.

It has your desired output. The trick is the subquery. You need to first identify the max fee for each coach. Then join the trainees in via the fee table.

Upvotes: 1

Related Questions