hello stackoverflow community :) ,
I have a complex join query is causing me lots of troubles :/
i have 3 tables here.
1: table [taxonomys t]
id ownerId type
1 1 office
2 1 inventory
3 1 inventory_item
2: table [tax_links l]
id parent son
1 1 2
2 1 3
3: table [settings s]
id taxId title value type
1 1 name office1 taxonomy
2 1 location Address taxonomy
3 1 settings on tax_links
so 1. taxonomy table containes all resourses of a user 2. link_taxs link 2 taxonomys to each others 3. settings save settings for resource , in case i want settings to be related to relations (not global) i set type in settings to tax_links
My query should return all resources of user, and concentrate all related as sons, and the id of relations in relsId.
SELECT `t`.`id`, group_concat(l.son) as sons, group_concat(l.id) as relsId, group_concat( s.title ) as titles, group_concat( s.value ) as vals, `t`.`name`, `t`.`type`, group_concat(s.id) as sid
FROM (`taxonomys` t)
LEFT JOIN `tax_links` l ON `l`.`parent` = `t`.`id`
LEFT JOIN `settings` s ON `s`.`taxId` = `t`.`id` and s.table = 'taxonomy'
WHERE `t`.`ownerId` = 1
GROUP BY `t`.`id`
it runs perfect, and return all what i need EXCEPT THAT it return REPLICATED results in sons,relsId.
for example tables i provided when i run this query i expect the result to be
id sons relsId titles vals
1 2,3 1,2 name,location office1,address
problem is when i run my query it return duplicate content for sons and relsId so i get something like
id sons relsId titles vals
1 2,3,2,3 1,2 name,location office1,address
name,location office1,address
why is this happeing ? i know i can filter array_unique using php after i fetch row, but what am i doing wrong ?
You want to use the distinct keyword in group_concat():
SELECT `t`.`id`, group_concat(distinct l.son) as sons,
group_concat(distinct l.id) as relsId,
group_concat( s.title ) as titles, group_concat( s.value ) as vals,
`t`.`name`, `t`.`type`, group_concat(s.id) as sid