Mysql is duplicating search result

Asked

Viewed 103 times

3

I’m having difficulties to assemble a query.

I have a database more or less in this structure.

Table1 Table2 Table3 Table4

I need to mount a query that picks the results in the 4 tables, for this I used LEFT JOIN, because in tables2, 3 and 4 may not exist.

I did 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 the following, tables 2, 3 and 4 may have different id’s with different values, but associated with the id of Tabela1 and this is duplicating the id of Tabela1 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 that 'Z' comes out on the same line as 'X''.

Is that possible?

mysql

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

  • The GROUP_CONCAT worked, but I fell into another problem. There in table2, for example, I have a field called status that can come as 'Null', so the search result is coming off more or less like this: tid1 tid2 | status if the field is 'Null' it does not concatenate in the correct way and I cannot identify if this status is from TID1 or TID2.

  • 1

    I don’t know how you’re using the status field, but you can try using the NULLIF(status,'')

  • I got it like this: GROUP_CONCAT(ifnull(c.Arp, 'null') SEPARATOR&') Arp . The only problem is that this field can be empty or null... So I have to find a way to turn the empty fields into 'null' in the result.

  • Not understood you want to check the two conditions? S3 is null or empty

  • 1

    See if it helps ,http://stackoverflow.com/a/17832946/2740371 SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1 
from tablename

  • 1

    It did work, the idea was to do the following... For example, the search returned with tid1&tid2' | 'status1&Status2', I want to associate to status1 to tid1 and Status2 to tid2 via PHP giving an explosion. But if the first value was empty it would concatenate like this: &Status2, then the explode would not work the way I wanted it to, The command looks like this: GROUP_CONCAT(IF(c.Arp IS NULL OR c.Arp = ', 'null', c.Arp) SEPARATOR '&') Arp,

  • 1

    Thank you very much!!!

Show 2 more comments

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.