0

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
Rohan Kumar
  • 726
  • 10
  • 17
  • How many conditions might you have? – Nick May 08 '19 at 06:53
  • I might have about 7 or 8 of so conditions based on different columns. If I have a marker string for them (which says if that record matches the condition "CONDITION_1"), then if that record matches another then the resulting column should become "CONDITION_1 CONDITION 2" for that ID field. – Rohan Kumar May 08 '19 at 06:55
  • PL/SQL and MySQL are two *very different things*. Please tag with the database you are really using. – Gordon Linoff May 08 '19 at 11:35
  • Thanks for calling it out @GordonLinoff ! https://techdifferences.com/difference-between-sql-and-pl-sql.html I'll remove the tag. Also, your SO profile is too goood! I aspire to have a similar one in a few years. Any resources you'd recommend for it? Thank you. :) – Rohan Kumar May 10 '19 at 13:32

1 Answers1

2

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
Nick
  • 138,499
  • 22
  • 57
  • 95
  • A stored procedure would mean creating an extra column in existing table? – Rohan Kumar May 10 '19 at 13:27
  • I've marked as accepted as the dbfiddle demo seems to be my use case. I'll update if it fails in actual testing. Thank you for the speed, Nick! – Rohan Kumar May 10 '19 at 13:29
  • @RohanKumar no worries. No need to accept unless you're completely happy with the answer. I notice you changed the tagging on your question so I've updated the [demo](https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=5babb4a4d7e70b7c6da0ab54909e84f4) to postgresql (it doesn't actually change the query though). – Nick May 11 '19 at 00:25
  • @RohanKumar in terms of a stored procedure, it doesn't have to be an extra column, you could create a procedure that created the condition query dynamically (e.g. you might call it as `compare_columns('foo', 'bar')`) and it would produce similar results to the above query. – Nick May 11 '19 at 00:28
  • Do you have Redshift experience? I thought because Redshift runs on Postgres, so concat_ws would work. Turns out it doesn't work. And i'm facing issues when I try and use nested-concats on Case statements (field empty in result). – Rohan Kumar May 13 '19 at 14:13
  • @RohanKumar looks like you need to use `CONCAT` instead e.g. `CONCAT(CASE WHEN t1.foo != t2.foo THEN 'foo' END, ' ', CASE WHEN t1.bar != t2.bar THEN 'bar' END) AS reason` – Nick May 13 '19 at 14:16
  • tried that. The field returns empty. Seems like `case` statement within `concat` doesn't seem to work (because it's not static?) – Rohan Kumar May 13 '19 at 14:21
  • 1
    @RohanKumar `concat` doesn't care if its arguments aren't static. you might need to use `ELSE` clauses to deal with `NULL` values: https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=c6064d18ddc219aeea509312c09d4395 – Nick May 13 '19 at 14:27
  • Adding `ELSE ''` worked. I checked by removing `ELSE ''` parts to test and it's really because of `ELSE ''`. Also, you might be right, concat shouldn't care as long as the expression within provides a varchar. Also, Redshift concat accepts not more than 2 arguments, so require nested concats. Again with same `ELSE ''` as your dbfiddle example illustrates! Thanks! – Rohan Kumar May 14 '19 at 06:38
  • 1
    @RohanKumar I'm glad to hear that solved the issue. `CONCAT_WS` automatically ignores `NULL` values, that's why my original query didn't need the `ELSE` clauses. – Nick May 14 '19 at 07:09