MYSQL View with LEFT JOIN return all possible combinations

join;MySQLview

I am working on a view that joins multiple table. The data will be entered on separate table based on RepairID. The view will gather the data that is linking to the RepairID. My problem is after I enter the data, the view will show all the probability of the records output. Below are my code of view:

SELECT 
    `new`.`SRPartsID` AS `SRPartsID`,
    `new`.`RepairID` AS `RepairID`,
    `new`.`SRNo` AS `SRNo`,
    `new`.`DateReceived` AS `DateReceived`,
    `new`.`ShipmentDate` AS `ShipmentDate`,
    `tb1stdebug`.`FirstDebugTestingErrorCode` AS `FirstDebugTestingErrorCode`,
    `tb1stdebug`.`FirstDebugActionTaken` AS `FirstDebugActionTaken`,
    `tb1stdebug`.`FirstDebugComponentLocation` AS `FirstDebugComponentLocation`,
    `tb2nddebug`.`SecondDebugTestingErrorCode` AS `SecondDebugTestingErrorCode`,
    `tb2nddebug`.`SecondDebugActionTaken` AS `SecondDebugActionTaken`,
    `tb2nddebug`.`SecondDebugComponentLocation` AS `SecondDebugComponentLocation`,
    `tb3rddebug`.`ThirdDebugTestingErrorCode` AS `ThirdDebugTestingErrorCode`,
    `tb3rddebug`.`ThirdDebugActionTaken` AS `ThirdDebugActionTaken`,
    `tb3rddebug`.`ThirdDebugComponentLocation` AS `ThirdDebugComponentLocation`,
    `tb1stfct`.`FirstFctTestingErrorCode` AS `FirstFctTestingErrorCode`,
    `tb1stfct`.`FirstFctActionTaken` AS `FirstFctActionTaken`,
    `tb1stfct`.`FirstFctComponentLocation` AS `FirstFctComponentLocation`,
    `tb2ndfct`.`SecondFctTestingErrorCode` AS `SecondFctTestingErrorCode`,
    `tb2ndfct`.`SecondFctActionTaken` AS `SecondFctActionTaken`,
    `tb2ndfct`.`SecondFctComponentLocation` AS `SecondFctComponentLocation`,
    `tb3rdfct`.`ThirdFctTestingErrorCode` AS `ThirdFctTestingErrorCode`,
    `tb3rdfct`.`ThirdFctActionTaken` AS `ThirdFctActionTaken`,
    `tb3rdfct`.`ThirdFctComponentLocation` AS `ThirdFctComponentLocation`
FROM
    ((((((`tbsrparts_new` `new`
    LEFT JOIN `tb1stdebug` ON ((`new`.`RepairID` = `tb1stdebug`.`RepairID`)))
    LEFT JOIN `tb2nddebug` ON ((`new`.`RepairID` = `tb2nddebug`.`RepairID`)))
    LEFT JOIN `tb3rddebug` ON ((`new`.`RepairID` = `tb3rddebug`.`RepairID`)))
    LEFT JOIN `tb1stfct` ON ((`new`.`RepairID` = `tb1stfct`.`RepairID`)))
    LEFT JOIN `tb2ndfct` ON ((`new`.`RepairID` = `tb2ndfct`.`RepairID`)))
    LEFT JOIN `tb3rdfct` ON ((`new`.`RepairID` = `tb3rdfct`.`RepairID`)))
ORDER BY `new`.`SRPartsID` DESC

This is my table structure

tbsrparts_new

+-------------------------------+--------------------+------+-----+---------+----------------+
|             Field             |        Type        | Null | Key | Default |     Extra      |
+-------------------------------+--------------------+------+-----+---------+----------------+
| SRPartsID                     | "int(10) unsigned" | NO   | PRI | NULL    | auto_increment |
| RepairID                      | varchar(200)       | NO   |     | NULL    |                |
| SRNo                          | varchar(200)       | YES  | MUL | NULL    |                |
| DateReceived                  | varchar(200)       | YES  |     | NULL    |                |
| ShipmentDate                  | varchar(200)       | YES  |     | NULL    
+-------------------------------+--------------------+------+-----+---------+----------------+

tb1stdebug

+-----------------------------+--------------------+------+-----+---------+----------------+
|            Field            |        Type        | Null | Key | Default |     Extra      |
+-----------------------------+--------------------+------+-----+---------+----------------+
| FirstDebugID                | "int(10) unsigned" | NO   | PRI | NULL    | auto_increment |
| RepairID                    | varchar(500)       | YES  |     | NULL    |                |
| FirstDebugTestingErrorCode  | varchar(500)       | YES  | MUL | NULL    |                |
| FirstDebugActionTaken       | varchar(500)       | YES  |     | NULL    |                |
| FirstDebugComponentLocation | varchar(500)       | YES  |     | NULL    |                |
+-----------------------------+--------------------+------+-----+---------+----------------+

tb2nddebug

+-----------------------------+--------------------+------+-----+---------+----------------+
|            Field            |        Type        | Null | Key | Default |     Extra      |
+-----------------------------+--------------------+------+-----+---------+----------------+
| SecondDebugID                | "int(10) unsigned" | NO   | PRI | NULL    | auto_increment |
| RepairID                     | varchar(500)       | YES  |     | NULL    |                |
| SecondDebugTestingErrorCode  | varchar(500)       | YES  | MUL | NULL    |                |
| SecondDebugActionTaken       | varchar(500)       | YES  |     | NULL    |                |
| SecondDebugComponentLocation | varchar(500)       | YES  |     | NULL    |                |
+-----------------------------+--------------------+------+-----+---------+----------------+

tb3rddebug

+-----------------------------+--------------------+------+-----+---------+----------------+
|            Field            |        Type        | Null | Key | Default |     Extra      |
+-----------------------------+--------------------+------+-----+---------+----------------+
| ThirdDebugID                | "int(10) unsigned" | NO   | PRI | NULL    | auto_increment |
| RepairID                    | varchar(500)       | YES  |     | NULL    |                |
| ThirdDebugTestingErrorCode  | varchar(500)       | YES  | MUL | NULL    |                |
| ThirdDebugActionTaken       | varchar(500)       | YES  |     | NULL    |                |
| ThirdDebugComponentLocation | varchar(500)       | YES  |     | NULL    |                |
+-----------------------------+--------------------+------+-----+---------+----------------+

tb1stfct

+---------------------------+--------------------+------+-----+---------+----------------+
|           Field           |        Type        | Null | Key | Default |     Extra      |
+---------------------------+--------------------+------+-----+---------+----------------+
| FirstFctID                | "int(10) unsigned" | NO   | PRI | NULL    | auto_increment |
| RepairID                  | varchar(45)        | YES  |     | NULL    |                |
| FirstFctTestingErrorCode  | varchar(500)       | YES  | MUL | NULL    |                |
| FirstFctActionTaken       | varchar(500)       | YES  |     | NULL    |                |
| FirstFctComponentLocation | varchar(500)       | YES  |     | NULL    |                |
+---------------------------+--------------------+------+-----+---------+----------------+

tb2ndfct

+----------------------------+--------------------+------+-----+---------+----------------+
|           Field            |        Type        | Null | Key | Default |     Extra      |
+----------------------------+--------------------+------+-----+---------+----------------+
| SecondFctID                | "int(10) unsigned" | NO   | PRI | NULL    | auto_increment |
| RepairID                   | varchar(45)        | YES  |     | NULL    |                |
| SecondFctTestingErrorCode  | varchar(500)       | YES  | MUL | NULL    |                |
| SecondFctActionTaken       | varchar(500)       | YES  |     | NULL    |                |
| SecondFctComponentLocation | varchar(500)       | YES  |     | NULL    |                |
+----------------------------+--------------------+------+-----+---------+----------------+

tb3rdfct

+---------------------------+--------------------+------+-----+---------+----------------+
|           Field           |        Type        | Null | Key | Default |     Extra      |
+---------------------------+--------------------+------+-----+---------+----------------+
| ThirdFctID                | "int(10) unsigned" | NO   | PRI | NULL    | auto_increment |
| RepairID                  | varchar(45)        | YES  |     | NULL    |                |
| ThirdFctTestingErrorCode  | varchar(500)       | YES  | MUL | NULL    |                |
| ThirdFctActionTaken       | varchar(500)       | YES  |     | NULL    |                |
| ThirdFctComponentLocation | varchar(500)       | YES  |     | NULL    |                |
+---------------------------+--------------------+------+-----+---------+----------------+

This is my sample data inserted to the db

tbsrparts_new

+-----------+----------+-------+--------------+--------------+
| SRPartsID | RepairID | SRNo  | DateReceived | ShipmentDate |
+-----------+----------+-------+--------------+--------------+
|     26050 |    26041 | test2 | 02,July,2015 | Pending      |
+-----------+----------+-------+--------------+--------------+

tb1stdebug

+--------------+----------+----------------------------+-----------------------+-----------------------------+
| FirstDebugID | RepairID | FirstDebugTestingErrorCode | FirstDebugActionTaken | FirstDebugComponentLocation |
+--------------+----------+----------------------------+-----------------------+-----------------------------+
|        26048 |    26041 | T00111                     | Touch Up              | -                           |
|        26049 |    26041 | T02222                     | Retest                | -                           |
|        26053 |    26041 | T08888                     | Touch Up              | -                           |
+--------------+----------+----------------------------+-----------------------+-----------------------------+

tb2nddebug

+---------------+----------+-----------------------------+------------------------+------------------------------+
| SecondDebugID | RepairID | SecondDebugTestingErrorCode | SecondDebugActionTaken | SecondDebugComponentLocation |
+---------------+----------+-----------------------------+------------------------+------------------------------+
|            12 |    26041 | T03333                      | Touch Up               | -                            |
|            13 |    26041 | T04444                      | Retest                 | -                            |
|            14 |    26041 | T08888                      | Touch Up               | -                            |
+---------------+----------+-----------------------------+------------------------+------------------------------+

tb3rddebug

+--------------+----------+----------------------------+-----------------------+-----------------------------+
| ThirdDebugID | RepairID | ThirdDebugTestingErrorCode | ThirdDebugActionTaken | ThirdDebugComponentLocation |
+--------------+----------+----------------------------+-----------------------+-----------------------------+
|           12 |    26041 | T03333                     | Touch Up              | -                           |
|           13 |    26041 | T04444                     | Retest                | -                           |
|           14 |    26041 | T08888                     | Touch Up              | -                           |
+--------------+----------+----------------------------+-----------------------+-----------------------------+

tb1stfct

+------------+----------+--------------------------+---------------------+---------------------------+
| FirstFctID | RepairID | FirstFctTestingErrorCode | FirstFctActionTaken | FirstFctComponentLocation |
+------------+----------+--------------------------+---------------------+---------------------------+
|      26052 |    26041 | T08888                   | Retest              | -                         |
+------------+----------+--------------------------+---------------------+---------------------------+

tb2ndfct

+-------------+----------+---------------------------+----------------------+----------------------------+
| SecondFctID | RepairID | SecondFctTestingErrorCode | SecondFctActionTaken | SecondFctComponentLocation |
+-------------+----------+---------------------------+----------------------+----------------------------+
|           9 |    26041 | T08888                    | Touch Up             | -                          |
|          10 |    26041 | T88889                    | Retest               | -                          |
+-------------+----------+---------------------------+----------------------+----------------------------+

tb3rdfct

+------------+----------+--------------------------+---------------------+---------------------------+
| ThirdFctID | RepairID | ThirdFctTestingErrorCode | ThirdFctActionTaken | ThirdFctComponentLocation |
+------------+----------+--------------------------+---------------------+---------------------------+
|          7 |    26041 | T08888                   | Touch Up            | -                         |
+------------+----------+--------------------------+---------------------+---------------------------+

The output on the view shows all the probability of the combination since some of the table has more than 1 records that referring to the same RepairID. Below are the output in image format. I cant paste the table because it reached the maximum characters.

Output

I have also create the schema on sqlfiddle with my view query : http://www.sqlfiddle.com/#!9/d155d/7

Since it returning too many rows, its hard for me to analyse the data. I am not sure how to reduce the number of rows. I don't want it to show all the possible combination. Can anyone help me??

This is the output i want

    +-------+---------------+--------------+----------------------------+-----------------------+-----------------------------+-----------------------------+------------------------+------------------------------+----------------------------+-----------------------+-----------------------------+--------------------------+---------------------+---------------------------+---------------------------+----------------------+----------------------------+--------------------------+---------------------+---------------------------+
| SRNo  | DateReceived  | ShipmentDate | FirstDebugTestingErrorCode | FirstDebugActionTaken | FirstDebugComponentLocation | SecondDebugTestingErrorCode | SecondDebugActionTaken | SecondDebugComponentLocation | ThirdDebugTestingErrorCode | ThirdDebugActionTaken | ThirdDebugComponentLocation | FirstFctTestingErrorCode | FirstFctActionTaken | FirstFctComponentLocation | SecondFctTestingErrorCode | SecondFctActionTaken | SecondFctComponentLocation | ThirdFctTestingErrorCode | ThirdFctActionTaken | ThirdFctComponentLocation |
+-------+---------------+--------------+----------------------------+-----------------------+-----------------------------+-----------------------------+------------------------+------------------------------+----------------------------+-----------------------+-----------------------------+--------------------------+---------------------+---------------------------+---------------------------+----------------------+----------------------------+--------------------------+---------------------+---------------------------+
| test2 | 2, July, 2015 | Pending      | T00111                     | Touch Up              | -                           | T03333                      | Touch Up               | -                            | T05555                     | Touch Up              | -                           | T08888                   | Retest              | -                         | T08888                    | Touch Up             | -                          | T08888                   | Touch Up            | -                         |
| test2 | 2, July, 2015 | Pending      | T02222                     | Retest                | -                           | T04444                      | Retest                 | -                            | T06666                     | Retest                | -                           | T08888                   | Retest              | -                         | T08889                    | Retest               | -                          | T08888                   | Touch Up            | -                         |
| test2 | 2, July, 2015 | Pending      | T08888                     | Touch Up              | -                           | T08888                      | Touch Up               | -                            | T08888                     | Touch Up              | -                           | T08888                   | Retest              | -                         | T08889                    | Retest               | -                          | T08888                   | Touch Up            | -                         |
+-------+---------------+--------------+----------------------------+-----------------------+-----------------------------+-----------------------------+------------------------+------------------------------+----------------------------+-----------------------+-----------------------------+--------------------------+---------------------+---------------------------+---------------------------+----------------------+----------------------------+--------------------------+---------------------+---------------------------+

Best Answer

To get the output you mentioned, you may run the following query. Basically, it is the same as yours with GROUP BY added:

SELECT 
    `new`.`SRPartsID` AS `SRPartsID`,
    `new`.`RepairID` AS `RepairID`,
    `new`.`SRNo` AS `SRNo`,
    `new`.`DateReceived` AS `DateReceived`,
    `new`.`ShipmentDate` AS `ShipmentDate`,
    `tb1stdebug`.`FirstDebugTestingErrorCode` AS `FirstDebugTestingErrorCode`,
    `tb1stdebug`.`FirstDebugActionTaken` AS `FirstDebugActionTaken`,
    `tb1stdebug`.`FirstDebugComponentLocation` AS `FirstDebugComponentLocation`,
    `tb2nddebug`.`SecondDebugTestingErrorCode` AS `SecondDebugTestingErrorCode`,
    `tb2nddebug`.`SecondDebugActionTaken` AS `SecondDebugActionTaken`,
    `tb2nddebug`.`SecondDebugComponentLocation` AS `SecondDebugComponentLocation`,
    `tb3rddebug`.`ThirdDebugTestingErrorCode` AS `ThirdDebugTestingErrorCode`,
    `tb3rddebug`.`ThirdDebugActionTaken` AS `ThirdDebugActionTaken`,
    `tb3rddebug`.`ThirdDebugComponentLocation` AS `ThirdDebugComponentLocation`,
    `tb1stfct`.`FirstFctTestingErrorCode` AS `FirstFctTestingErrorCode`,
    `tb1stfct`.`FirstFctActionTaken` AS `FirstFctActionTaken`,
    `tb1stfct`.`FirstFctComponentLocation` AS `FirstFctComponentLocation`,
    `tb2ndfct`.`SecondFctTestingErrorCode` AS `SecondFctTestingErrorCode`,
    `tb2ndfct`.`SecondFctActionTaken` AS `SecondFctActionTaken`,
    `tb2ndfct`.`SecondFctComponentLocation` AS `SecondFctComponentLocation`,
    `tb3rdfct`.`ThirdFctTestingErrorCode` AS `ThirdFctTestingErrorCode`,
    `tb3rdfct`.`ThirdFctActionTaken` AS `ThirdFctActionTaken`,
    `tb3rdfct`.`ThirdFctComponentLocation` AS `ThirdFctComponentLocation`
FROM
    ((((((`tbsrparts_new` `new`
    LEFT JOIN `tb1stdebug` ON ((`new`.`RepairID` = `tb1stdebug`.`RepairID`)))
    LEFT JOIN `tb2nddebug` ON ((`new`.`RepairID` = `tb2nddebug`.`RepairID`)))
    LEFT JOIN `tb3rddebug` ON ((`new`.`RepairID` = `tb3rddebug`.`RepairID`)))
    LEFT JOIN `tb1stfct` ON ((`new`.`RepairID` = `tb1stfct`.`RepairID`)))
    LEFT JOIN `tb2ndfct` ON ((`new`.`RepairID` = `tb2ndfct`.`RepairID`)))
    LEFT JOIN `tb3rdfct` ON ((`new`.`RepairID` = `tb3rdfct`.`RepairID`)))
GROUP BY SRNo, DateReceived, FirstDebugTestingErrorCode
ORDER BY `new`.`SRPartsID` DESC

However, this might be logically wrong, as you cannot guarantee what info from other fields/tables would show from the fields that are not in the group by part of the query.

Related Question