CODEWITHSUNDEEP
mysqlsqlselectmysql-5.7
yogi
yogi

Reputation: 3

Condtional select query if a value in coulmn is empty

I have a table

1

and I'm trying to write a select statement with ifnull condition for same type value like to get the result as shown in this table

2

Below is the sql I have tried -

SELECT type, memo, IFNULL( memo, type = type) memo_all, amount FROM table

But I get the same result as memo column in memo_all column with above query as can be seen in this table 3. Please find the sqlfiddle here with above query and table - http://sqlfiddle.com/#!9/55c43f

What am I missing here? Is there any alternative way to get the result as shown in table 2?

Upvotes: 0

Views: 61

Answers (3)

Akina
Akina

Reputation: 42661

SELECT t1.*, t2.memo memo_all
FROM `table` t1
JOIN `table` t2 USING (type)
WHERE t2.memo != '';

Solution which uses one source table copy:

SELECT `table`.*, @tmp := CASE WHEN memo = '' THEN @tmp ELSE memo END memo_all
FROM `table`, (SELECT @tmp := '') variable
ORDER BY type, memo DESC;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Use window functions:

SELECT type, memo,
       max(memo) over (partition by type) as memo_all
       amount
FROM table;

You want to "borrow" the value from another row. A simple scalar function is not going to do that. However, window functions provide this capability.

EDIT:

In older versions of MySQL, you can use a correlated subquery:

SELECT type, memo,
       COALESCE(memo,
                (SELECT t2.memo
                 FROM table t2
                 WHERE t2.type = t.type AND t2.memo IS NOT NULL
                 LIMIT 1
                )
               ) as memo_all
       amount
FROM table t;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521599

Just in case you are using a version of MySQL earlier than 8+, here is an alternative to Gordon's answer which doesn't use window functions:

SELECT
    t1.type,
    t1.memo,
    t2.memo AS memo_all,
    t1.amount
FROM yourTable t1
INNER JOIN
(
    SELECT type, MAX(memo) AS memo
    FROM yourTable
    GROUP BY type
) t2
    ON t1.type = t2.type;

Upvotes: 1

Related Questions