CODEWITHSUNDEEP
sql
Rock Star
Rock Star

Reputation: 45

using self joins to retrieve unique columns

Can anyone help me in solving the below problem.

Table1:

  MSISDN(PK) product_name  BASE_SOCBoo  PLAN_NAME   Limit

1   152     WHOADJTH2          N           ABC         10
2   152     WADADJTH5          N           ABC         20
2   152     THRWS33            Y           ABC        100
3   149     WADADJTH4          N           ABC          5
4   149     WADADJTH5          N           ABC          6

I am trying to show for an msisdn there will be product_name which is mandatory like (whoadjth2,whoadjth5,whoadjth4) and product_names THRWS31,THRWS32,THRWS33 are optional, when these exists base_soc_boo flag is shown as enabled and otherwise 'N'

when i tried the below sql i am getting the output which i very close

SELECT flex.msisdn,
  flex.product_name Flex_Soc,
  CASE
    WHEN base.product_name IN ('THRWS33', 'THRWS32', 'THRWS31')
    AND base.base_soc_boo   = 'Y'
    THEN base.product_name
    WHEN flex.product_name NOT IN ('THRWS33', 'THRWS32', 'THRWS31')
    AND flex.base_soc_boo       = 'N'
    THEN 'Not Prov'
  END base_soc,
  flex.plan_name,
  flex.limit
FROM table1,
  (SELECT b.product_name,
    b.plan_name,
    b.msisdn,
    b.base_soc_boo
  FROM table1 b
  WHERE product_name IN ('THRWS33', 'THRWS32', 'THRWS31')
  AND b.base_soc_boo IN ('Y')
  )base
WHERE flex.product_name <> base.product_name
AND flex.plan_name       = base.plan_name
AND flex.base_soc_boo   IN ('N','Y')

output is :

    MSISDN  FLEX_SOC    BASE_SOC PLAN_NAME Limit   

1   152    WHOADJTH2    THRWS33      ABC     10
2   152    WADADJTH5    THRWS33      ABC     20
3   149    WADADJTH4    THRWS31      ABC      5
4   149    WADADJTH5    THRWS31      ABC      6

for 149 where base_soc_boo flag is 'N' need to displayed as 'Not Provisioned'

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Your query seems overly complicated. In most databases, you can use window functions for this:

SELECT msisdn, flex_soc,
       coalesce(base_soc, 'Not Prov') as base_soc, plan_name, limit
from (SELECT flex.msisdn, flex.product_name as Flex_Soc,
             MAX(case when base_soc_boo = 'Y' then flex.product_name end) over
                 (partition by flex.msisdn) as base_soc,
            flex.plan_name, flex.limit
      FROM table1 flex
     ) flex
where base_soc_boo = 'N';

You don't specify a database so ANSI-compatible syntax seems reasonable.

+----+--------+-----------+----------+-----------+-------+
|    | MSISDN | FLEX_SOC  | BASE_SOC | PLAN_NAME | Limit |
+----+--------+-----------+----------+-----------+-------+
|  6 |    152 | THRWS33   | THRWS33  | ABC       | 10240 |
|  7 |    152 | WADADJTH5 | THRWS33  | ABC       |  4092 |
|  8 |    152 | WHOADJTH2 | THRWS33  | ABC       |  1024 |
|  9 |    149 | WADADJTH4 | Not Prov | ABC       |   512 |
| 10 |    149 | WADADJTH5 | Not Prov | ABC       |  1024 |
+----+--------+-----------+----------+-----------+-------+

Upvotes: 1

Related Questions