Merge two records of one table with a record of another

Question

Merge two records of one table with a record of another

Navigation

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

1

I am a newbie in this programming (I am in formation), look for the following, waiting for your help and clarification.

My system works as follows:

1st. table participante , where I store basic student data, names, surnames, identification cards, registration date

2nd. table participante_has_trabajo where I relate the Id of Participante with the id of trabajo

3rd. table trabajo where I store the name of the work and the unique data of both participants such as career, mention, core, the Id of the date in which it was registered since this is the one that will allow me to locate the records I want.

I make the query to the database in the following way:

SELECT * FROM  participante_has_trabajo 
inner join participante on participante_has_trabajo.participante_ipte = participante.ipte 
inner join trabajo on participante_has_trabajo.trabajo_id = trabajo.id  
inner join fecha_hora on trabajo.fecha_hora_idf = fecha_hora.idf 
inner join nucleo on trabajo.nucleo_idn = nucleo.idn 
inner join mencion on trabajo.mencion_idm = mencion.idm 
inner join carrera on mencion.carrera_idc = carrera.idc 
where fecha BETWEEN '2018-08-06' and '2018-08-06' ORDER BY trabajo.id

If I have two participants associated with a trabajo_id , in the table participant_has_work brings me the data but as individual records

What I want to do is, in the case that a job has two associated participants as it would do to join those two participants to a single job title

I appreciate your help a greeting ...

php

asked by Braian Jaimes 07.08.2018 в 01:26

source

2 answers

0

I tried the sentence you indicated to me ... and it actually worked (I learned something new): D

excellent the result, only when I include the data I will have to place spaces in each input after the data

(in the result of the sentence appear the participant's data together (green box) and separated with the "-" of the other participant, would like it to remain as the one in the blue box (it is worth noting that the data that is in the blue box already comes with spaces from the database I doing tests) that I would have to add to the sentence? Annex print.

last question ... in case you want a second group_concat in the same sentence, as you would in that case?

I am very grateful for all the help ... I had two days trying: D

answered by 07.08.2018 в 04:34

Error in Laravel Mysql 5.6 Connecting Python with the MySQL database provided by WAMP Server [duplicated]

score 0 · Accepted Answer

If it's like you said in the comment:

The result I want to obtain would be the following:

titulo                                          participantes
-----------------------------------------------------------------
LA SENSIBILIDAD ORGANIZACIONAL EN LA....        IVOR - Andrea

You can get the data you expect in the following way:

SELECT 
    t.nombretrabajo, 
    GROUP_CONCAT(p.pnombre SEPARATOR ' - ') participantes
FROM  participante_has_trabajo pht
    INNER JOIN participante p   ON   pht.participante_ipte = p.ipte 
    INNER JOIN trabajo t        ON   pht.trabajo_id = t.id  
    INNER JOIN fecha_hora fh    ON   t.fecha_hora_idf = fh.idf 
    INNER JOIN nucleo n         ON   t.nucleo_idn = n.idn 
    INNER JOIN mencion m        ON   t.mencion_idm = m.idm 
    INNER JOIN carrera c        ON   m.carrera_idc = c.idc 
WHERE f.fecha BETWEEN '2018-08-06' AND '2018-08-06' 
GROUP BY t.id
ORDER BY t.id;

The keys here are:

that you group for each column id of table trabajo
that you group with a separator the rows that would be repeated in terms of the name of the participant, using the grouping function GROUP_CONCAT

I have used short aliases in the tables, to write a query less verbose and easier to analyze. It is an extended practice to use the first letter or initials of each table as aliases.