So there are two tables in a database. I have to find out whichever rows have discrepancy based on certain conditions (in couple of cases that's just equality checking between fields). I report ID of those rows.

The problem is to also include the reasons in another column as to why that ID is reported. Because an id can be fail multiple conditions (like mismatch on two fields), I just wanted to include all of those reasons in another column.

Basic idea is to append all the mismatches in another column.

I've looked at several SO questions but they don't exactly my use case. So now I'm thinking it's not possible with SQL.

I searched Google for "enter dynamic column values based on conditions sql", and hit : SQL Conditional column data return in a select statement : This adds a static column

I also learned it's possible to add another column in SELECT with dynamic content like this:

SELECT id, CASE 
WHEN columnname "DEF" then "I" ELSE "YOU" newColumnName
FROM tableName

But I have not been able to find dynamic column value assignment and update SQL. That's the problem.

Expected results:

  1. I just want to be able to concat all the cases "strings" which a record is applicable for.
  2. Do this with the two tables.

So because I have two tables to work with I have to put these conditions in the WHERE sub-clause, and not in the SELECT one.

So, if for ID = 345, column "FOO_MAN" does not match between two tables, and column "BAR_TOO" also does not match between two tables, then?

Then I want my select clause to capture information like this:

ID      | REASON
345     | FOO_MAN BAR_TWO

1 Answers

1
Nick On Best Solutions

It's probably easier to build this type of query dynamically (e.g. using a stored procedure) based on the conditions you want to test, but here is a small example which shows how it can be done:

SELECT t1.id,
       CONCAT_WS(' ',
                 CASE WHEN t1.foo != t2.foo THEN 'foo' END,
                 CASE WHEN t1.bar != t2.bar THEN 'bar' END
                 ) AS reason
FROM t1
JOIN t2 ON t2.id = t1.id
WHERE t1.foo != t2.foo OR t1.bar != t2.bar

Output (for my demo on dbfiddle)

id  reason
2   foo
4   bar
5   foo bar