CODEWITHSUNDEEP
mysql
Stefano Schivari
Stefano Schivari

Reputation: 57

Merge mysql rows on conditions

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

Answers (2)

exussum
exussum

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

Thomas Kelley
Thomas Kelley

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

Related Questions