How to get part of a group of data that is repeated only once and the rest is repeated in MySQL?

Question

How to get part of a group of data that is repeated only once and the rest is repeated in MySQL?

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

It has the following tables:

inspection -----------------------------------
Columna Tipo            Nulo    Predeterminado  
id_inspection           int(9)  No           
machines_numbers        int(1)  No           
shift_shifts_id         int(9)  No           
group_groups_id         int(1)  No           
sample_samples_id       int(1)  No           
line_lines_id           int(1)  No           
elaborated_employer_id  int(6)  No           
auditored_employer_id   int(6)  Sí  NULL         
approved_employer_id    int(6)  Sí  NULL 
--------------------------------------------

inspections_shapes_registers------------
Columna Tipo              Nulo          
id_isr  int(9)            No             
creation_date_f           varchar(255)               
creation_date_nf          varchar(255)               
inspection_inspections_id int(9)                     
sr_shapes_registers_id    int(9)         
---------------------------------------

shapes_registers----------------------
Columna Tipo            Nulo    
id_shape_register       int(9)                   
data                    varchar(1000)    
machine_machines_id     varchar(1)       
st_shapes_templates_id  int(9)
--------------------------------------

To show the data based on a date and line I use the following query:

SELECT i.id_inspection, 
                       i.machines_numbers, 
                       sr.data,
                       isr.id_isr, isr.creation_date_f, isr.creation_date_nf,
                       s.description as shift_description,
                       g.description as group_description,
                       sa.description as sample_description,
                       l.description as line_description,
                       st.name as shape_description,
                       e.id_card
            FROM       inspections_shapes_registers AS isr 
            INNER JOIN inspections                  AS i 
            ON         isr.inspection_inspections_id = i.id_inspection 
            INNER JOIN shapes_registers AS sr 
            ON         isr.sr_shapes_registers_id = sr.id_shape_register
            INNER JOIN shifts as s
            ON         i.shift_shifts_id = s.id_shift       
            INNER JOIN groups as g
            ON         i.group_groups_id = g.id_group
            INNER JOIN samples as sa
            ON         i.sample_samples_id = sa.id_sample
            INNER JOIN lines_ as l
            ON         i.line_lines_id = l.id_line  
            INNER JOIN shapes_templates AS st 
            ON         st.id_shape_template = sr.st_shapes_templates_id
            INNER JOIN employees AS e 
            ON         i.elaborated_employer_id = e.id_card
            WHERE 
                isr.creation_date_nf = :inspectionDataDate
                AND i.line_lines_id = :inspectionDataLine
                AND i.shift_shifts_id = :inspectionDataShift

What I get:

1
5
{"inspection1111"}
1
2018-02-22 10:01 AM
2018-02-22
1er
A
Primera Muestra
A1
qq-1244
1233

1
5
{"inspection2222"}
2
2018-02-22 10:01 AM
2018-02-22
1er
A
Primera Muestra
A1
qq-1244
1233

1
5
{"inspection3333"}
3
2018-02-22 10:01 AM
2018-02-22
1er
A
Primera Muestra
A1
qq-1244
1233

1
5
{"inspection4444"}
4
2018-02-22 10:01 AM
2018-02-22
1er
A
Primera Muestra
A1
qq-1244
1233

1
5
{"inspection5555"}
5
2018-02-22 10:01 AM
2018-02-22
1er
A
Primera Muestra
A1
qq-1244
1233

What I would like to obtain, since only the value of the sdada ( {"inspection1111"} ) and the id.isr value followed by the ( {inspection} ) vary, but the second does not matter much, but rather the data that are repeated only once, that is, all the sr.data ( {inspection} ):

Result:

1
5
{"inspection1111"}
{"inspection2222"}
{"inspection3333"}
{"inspection4444"}
1
2018-02-22 10:01 AM
2018-02-22
1er
A
Primera Muestra
A1
qq-1244
1233

Relationships

Each record of INSPECTIONS you are associated with multiple SHAPES_REGISTER verifying that they have their ID . each {"inspection"} is a DATA that corresponds to a SHAPES_REGISTER

Update 3:37 pm

Note: The values for sr.data can exceed 1000 characters when concatenated together. I find the result incomplete

Result of @Marcos response

1
5
{"inspection1111"}
{"inspection2222"}
{"inspection3333"}
{"inspection4444"}
{
1
2018-02-22 10:01 AM
2018-02-22
1er
A
Primera Muestra
A1
qq-1244
1233

mysql php

asked by Victor Alvarado 22.02.2018 в 16:27

source

2 answers

0

To finish completing the answer, in my case the inspection string could reach up to 2000 concatenated characters.

Use this:

$setting = "SET SESSION group_concat_max_len = 1000000";

To set the length of the GROUP_CONCAT length

answered by 22.02.2018 в 21:06

Print list within another list how can I create this mysql query in doctrine is it possible?

score 2 · Accepted Answer

Based on this comment , to group the records you can use GROUP_CONCAT by combining it with GROUP BY

As far as you can see, you need to group them all those that have the same i.id_inspection . If you do not indicate how to group them, it might not be what you expected.

So for example:

SELECT i.id_inspection, 
         i.machines_numbers, 
         # Agrupamos todos los 'sr.data' en la columna 'data'
         GROUP_CONCAT(sr.data SEPARATOR ',') AS data,
         isr.id_isr, isr.creation_date_f, isr.creation_date_nf,
         s.description as shift_description,
         g.description as group_description,
         sa.description as sample_description,
         l.description as line_description,
         st.name as shape_description,
         e.id_card
    FROM       inspections_shapes_registers AS isr 
    INNER JOIN inspections                  AS i 
    ON         isr.inspection_inspections_id = i.id_inspection 
    INNER JOIN shapes_registers AS sr 
    ON         isr.sr_shapes_registers_id = sr.id_shape_register
    INNER JOIN shifts as s
    ON         i.shift_shifts_id = s.id_shift       
    INNER JOIN groups as g
    ON         i.group_groups_id = g.id_group
    INNER JOIN samples as sa
    ON         i.sample_samples_id = sa.id_sample
    INNER JOIN lines_ as l
    ON         i.line_lines_id = l.id_line  
    INNER JOIN shapes_templates AS st 
    ON         st.id_shape_template = sr.st_shapes_templates_id
    INNER JOIN employees AS e 
    ON         i.elaborated_employer_id = e.id_card
    WHERE 
        isr.creation_date_nf = :inspectionDataDate
        AND i.line_lines_id = :inspectionDataLine
        AND i.shift_shifts_id = :inspectionDataShift
    # Indicamos que queremos agrupar los registro que tengan el mismo 'i.id_inspection'
    GROUP BY i.id_inspection

The result should be the following:

1
5
{"inspection1111"},{"inspection2222"},{"inspection3333"},{"inspection4444"}
1
2018-02-22 10:01 AM
2018-02-22
1er
A
Primera Muestra
A1
qq-1244
1233