Reputation: 57
I have a table like :
id_rel | id_art | id_fam | id_cat | id_scat | id_marca | id_model | id_year
1 cw001 10 11 21 null null null
2 cw001 null null null 233 455 466
for a particular search query i need to combine it in a mysql view like this:
id_rel | id_art | id_fam | id_cat | id_scat | id_marca | id_model | id_year
1 cw001 10 11 21 233 455 466
So i have to 'delete' the null values and combine the values. Can someone explain if it's possibile and the best way to do that?
Explanation of first table : here i have some products (id_art) and family,category,subfamily relationship and in the second row brand model year relationship. The website that use this table i have two kind of search, search for family-category-subfamily or search for brands-models-years,i need to combine them so when i'm in a family of products i have to be able to see all models,brands and years related to article.
I hope to be clear,sorry for my english Thanks
Upvotes: 0
Views: 438
Reputation: 18576
SELECT
id_rel,
id_art,
COALESCE(SUM(id_fam),0),
COALESCE(SUM(id_cat),0),
COALESCE(SUM(id_scat),0),
COALESCE(SUM(id_marca),0),
COALESCE(SUM(id_model),0),
COALESCE(SUM(id_year),0)
FROM
table_name
GROUP BY
id_art;
Would sum or give 0.
Could you give a more complex example to make sure we get the correct SQL
Upvotes: 0
Reputation: 10302
In MySQL, the MAX()
function will discard null
values. So you could do something like:
SELECT
id_rel,
id_art,
MAX(id_fam),
MAX(id_cat),
MAX(id_scat),
MAX(id_marca),
MAX(id_model),
MAX(id_year)
FROM
table_name
GROUP BY
id_art;
This assumes, of course, that the values will only ever be 1
or null
. If you start introducing other values, it's [potentially] a little harder.
Upvotes: 2