Search code examples
sql

select query to lookup column data against other column and extract value against other column in SQL

master_code id type number
null abcsjjjjsUV doo 852456
abcsjjjjsUV iydjfjdww too 951753
abcsjjjjsUV retslslfdf too null

for a given table,
if type = 'doo' then number and type = 'too' and number is not null then number
number as it is.

but if type = 'too' and number is null then for that record map it's master_code in id column with record type = 'doo', and corresponding number needs to populate instead of null.

expected output for, if type = 'too' and number is null then for that record map it's master_code in id column with record type = 'doo', and corresponding number needs to populate instead of null.

master_code id type number
null abcsjjjjsUV doo 852456
abcsjjjjsUV iydjfjdww too 951753
abcsjjjjsUV retslslfdf too 852456
Solution

  • Looks like you need a left-join with some conditions

    SELECT
  t.master_code,
  t.id,
  t.type,
  IFNULL(t.number, t2.number) AS number
FROM YourTable t
LEFT JOIN YourTable t2
  ON t2.id = t.master_code
 AND t.type = 'too'
 AND t.number IS NULL
 AND t2.type = 'doo'