Get name for different status with same ID in SQL

Crimeiaman 0
Viewed 41

I have a dataset with ID_1 and ID_2 who are individuals and a different STATUS for same ID_1 and ID_2 like this:

Original dataset

And I want to assign the NAME with the same ID_1 and ID_2 for the second STATUS, like this:

Modified dataset

SELECT * FROM example WHERE (status = 1 and status = 2)
sql postgresql
1 Answers

If we perform a self join (Substitute your tableName in where it says "TableName" below; we join the table to itself. Each is it's own data sets and since we sometimes want the name from the "related" records; a left join is needed. Together based on ID_1, and ID_2; but only when the the status on the second table is 1 greater than the first table so all status 2 join with status 1 for the same ID_1 and ID_2. When the status is 1 then no join occurs.. We then use coalesce to return the the desired name (one from 'B' table if such a join occurred, otherwise the base name from table aliased 'A'.

SELECT A.Index, A.ID_1, A.ID_2, A.Name, coalesce(B.Name, A.Name) as Name_P 
FROM TableName A 
LEFT JOIN tableName B  
  on A.ID_1=B.ID_1 
 and A.ID_2=B.ID_2 
 and A.Status-1 = B.Status  --This is changed from comment having give it more thought...

In theory (as this is untested without complete DDL and test data. This could be provided on one of my dbfiddle sites https://dbfiddle.uk/ for example.)

Index 1-7 would always return Robert for coalesce(B.Name, A.Name) [using A.Name value] because the join would find no status 0 records with ID_1 and ID_2 matching. So B.Name would be Null, and A.Name (Robert) would be returned; thus is the nature of coalesce() in this example.

For index 8-9, B.Name would have a value of Robert so it would be returned. Coalesce() returns the first non-null value in the series coalesce(value1, value2,value3, value...)

But I'm assuming several things here.

  • status is always separated by 1
  • status is numeric
  • there are no status 0
  • if status 3 exists, you'd want it related to status 2 names.
  • several other things which you may not have provided sample data for
  • several other edge cases which may result in an improper result.

We need to understand your data and situation better in order to know how to help you.

For example if index 10 "Fred" Exists with ID_1 111 and ID_2 of 1 and status 3... what should the Name_P be? Justin? Robert? neither? Both?

Now I don't' know if you're doing this to return data in a set, or if you need the Name_P as some sort of "Extra" so for now I'm returning as part of the data set. If you want more: clarify the question by providing sample data expected results and WHY those results. Where it gets challenging is defining limits on the data could you have status 3 and what happens then? or are we just working with status 1 & 2... things we need to understand Consider the complete DDL & Fiddle with sample data to help us help you so we can test our "attempts" against data you have where you know what the expected results are. The image is ok; but DDL and a minimally complete verifiable example is better.

xQbert0
Related