1

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            | -                         |
+-------+---------------+--------------+----------------------------+-----------------------+-----------------------------+-----------------------------+------------------------+------------------------------+----------------------------+-----------------------+-----------------------------+--------------------------+---------------------+---------------------------+---------------------------+----------------------+----------------------------+--------------------------+---------------------+---------------------------+
4
  • And what is the output that you want?
    – oNare
    Commented Jul 7, 2015 at 11:45
  • The output is right. In the example you have, there are 7 tables with the following count per table, for the same Repair_ID: (1, 3, 3, 3, 1, 2,1). If you multiply the numbers, you will get (54), which is the same number of row you got in the result. Commented Jul 7, 2015 at 15:13
  • Hi @oNare I have edited the question with the output i want. Is there any way to get it? Commented Jul 8, 2015 at 3:02
  • Hi @JehadKeriaki, I have edited the question with the output i want. Is there any way to get it? Commented Jul 8, 2015 at 3:03

1 Answer 1

1

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.

2
  • I did try this, but it only can works on works for table tb1stdebug, for other tables, it selects same data. Is this can be done by using union? Commented Jul 9, 2015 at 0:58
  • When a "left join" introduces multiple lines because one field happen to have multiple values for a given id : GROUP BY: only shows the first line. How can we make it show "all the values for the problematic field, concatenated" in that field, instead of just the first occurence? Commented Jan 26, 2017 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.