CODEWITHSUNDEEP
phpmysqlsql
TARA
TARA

Reputation: 527

SQL distinct inner join

i dont know about this sql, can sql do this

i've table

table : tree_hie 

cluster | id 
1       | X | Y
2       | X | Y | Z
3       | A | B

table : value 

id | value 
x  | 3
y  | 3
z  | 3
a  | 0
b  | 0

and i want to display like this 

cluster | value | id 
1       | 3     | x,y
2       | 3     | x,y,z
3       | 0     |a,b

may you know how to make query like this .. i dont have any idea .. thank you so much..

Upvotes: 2

Views: 67

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can also use replace to first replace spaces and | pipe with comma then you can use find_in_set

select t.cluster,
min(v.`value`) as value,
group_concat(v.id order by v.id) as id
from tree_hie t
join `value` v on (
find_in_set(
  v.id,replace(replace(t.id,'|',','),' ','') 
  ) > 0
) 
group by t.cluster

But its really bad to have a structure like this you should care to have a normalized data structure first

DEMO

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If I understand correctly, you need a join between the two tables:

select th.cluster,
       min(v.value) as value,
       group_concat(v.id) as ids
from tree_hie th join
     value v
     on concat('|', th.id, '|') like concat('%|', v.id, '|%')
group by th.cluster;

You have stored a list of ids in a single field, with a vertical bar as a separator. This is a bad data structure. You should be using a junction table instead, with one row per cluster and single id.

EDIT:

If the separator is really ' | ' with spaces:

     on concat('| ', th.id, ' |') like concat('%| ', v.id, ' |%')

Upvotes: 1

Related Questions