MySQL is duplicating search result

Navigation
3

I'm having a hard time putting up a query.

I have a Database more or less in this structure.

Table 1 Table 2 Table 3 Table 4

I need to set up a query that takes the results in the 4 tables, for this I used the LEFT JOIN, because in tables 2, 3 and 4 it may not exist.

I made a query like this: 

select distinct id_tabela1,
tid_tabela2, tid_tabela3, tid_tabela4 
from tabela1 t1  
left join tabela2 t2 on t1.id_tabela1 = t2.tid_tabela2 
left join tabela3 t3 on t1.id_tabela1 = t3.tid_tabela3 
left join tabela4 t4 on t1.id_tabela1 = t4.tid_tabela4 
where...

What is happening is as follows, tables 2, 3 and 4 may have different id's with different values, but associated with the id of table1 and this is duplicating the id of table1 in the search result.

An example of how it's coming out: 

id_tabela1 | tid_tabela2 | tid_tabela3 | tid_tabela4
1          | x           | null        | null
1          | z           | null        | null

What I need is for this 'Z' to be on the same line as 'X'.

Is it possible?

    
asked by anonymous 28.10.2016 / 02:27

1 answer

4

You can use the GROUP_CONCAT to group and concatenate your string as follows. 

select id_tabela1, GROUP_CONCAT(tid_tabela2 SEPARATOR ' ') as tid_tabela2 
, GROUP_CONCAT(tid_tabela3 SEPARATOR ' ') as tid_tabela3  ,  GROUP_CONCAT(tid_tabela4 SEPARATOR ' ') as tid_tabela4 
from tabela1 t1  
left join tabela2 t2 on t1.id_tabela1 = t2.tid_tabela2 
left join tabela3 t3 on t1.id_tabela1 = t3.tid_tabela3 
left join tabela4 t4 on t1.id_tabela1 = t4.tid_tabela4 
where... seu filtro
group by id_tabela1
    
28.10.2016 / 02:42
How to work with lock in SQL records? How to display / hide fields according to selection?