SQL, query that finds if two given names have the same number in one column

Question

I have been trying to solve this for about a week now. I really need someone's help.

I will be given two or three "LN"s and I have to check if they have the same number in the column "FODEX". Then maybe just add a column and print one if they have the same FODEX, and print zero if not.

I wrote this query:

 SELECT  MSTR.LN , LINK.SEQNO, MMA5_MSTR.FODEX, MMA5_MSTR.DES, MSTR.SL
 FROM  (((MSTR INNER JOIN LINK
 ON MSTR.SEQNO = LINK.SEQNO) INNER JOIN MMA5_MSTR
 ON LINK.FODEX = MMA5_MSTR.FODEX))
 WHERE (MSTR.LN LIKE "%NOVOLLL%" OR MSTR.LN LIKE "%TRES%" OR MSTR.LN LIKE 
 "%ENBREL%")
 GROUP BY SEQNO, SL
 ORDER BY FODEX;

This is the output I get,

  LN       || SEQNO       ||   FODEX  ||     DES     ||  SL
 ----------------------------------------------------------------   
 TRES      || 71842       ||    13    ||  ANTIDIABE  ||  12
 NOVOLLL   || 44340       ||    13    ||  ANTIDIABE  ||  12
 NOVOLLL   || 44340       ||   1059   ||  ANTIDIABE  ||  55
 TRES      || 71842       ||   1059   ||  ANTIDIABE  ||  55
 TRES      || 71842       ||   1317   ||  ANTIDIABE  ||  66
 NOVOLLL   || 44340       ||   1317   ||  ANTIDIAB   ||  66
 ENBREL    || 40869       ||   1722   ||  TNe        ||  90
 ENBREL    || 40869       ||   29359  ||  IMMUNOSUPP ||  103

This is the output I am trying to get,

  LN       || SEQNO       ||   FODEX  ||     DES     ||  SL || Y/N
 ----------------------------------------------------------------   
 TRES      || 71842       ||    13    ||  ANTIDIABE  ||  12 || 1
 NOVOLLL   || 44340       ||    13    ||  ANTIDIABE  ||  12 || 1
 NOVOLLL   || 44340       ||   1059   ||  ANTIDIABE  ||  55 || 1
 TRES      || 71842       ||   1059   ||  ANTIDIABE  ||  55 || 1
 TRES      || 71842       ||   1317   ||  ANTIDIABE  ||  66 || 1
 NOVOLLL   || 44340       ||   1317   ||  ANTIDIAB   ||  66 || 1
 ENBREL    || 40869       ||   1722   ||  TNe        ||  90 || 0
 ENBREL    || 40869       ||   29359  ||  IMMUNOSUPP ||  103|| 0

What's the expected result on the extra column? Can you add it to the example? — The Impaler, Aug 20 '19 at 01:26
@YousefSameer. . . Your aggregation query makes no sense. You have unaggregated columns in the `SELECT` -- that is, the `SELECT` columns and `GROUP BY` columns are not consistent. — Gordon Linoff, Aug 20 '19 at 10:33

score 0 · Answer 1 · answered Aug 20 '19 at 02:22

0

I think you can do what you want with aggregation:

SELECT MSTR.LN,
       MAX(MMA5_MSTR.FODEX) = MIN(MMA5_MSTR.FODEX) as is_same_flag
FROM MSTR INNER JOIN
     LINK
     ON MSTR.SEQNO = LINK.SEQNO INNER JOIN
     MMA5_MSTR
     ON LINK.FODEX = MMA5_MSTR.FODEX
WHERE MSTR.LN LIKE '%NOVOLLL%' OR
      MSTR.LN LIKE '%TRES%' OR
      MSTR.LN LIKE '%ENBREL%'
GROUP BY LN;

This doesn't add an extra column but it seems to do what you want.

answered Aug 20 '19 at 02:22

Gordon Linoff

1,242,037
58
646
786

Could you please check the example of the output I added to my question. I really appreciate your help – Data_sniffer Aug 20 '19 at 03:14

score 0 · Answer 2 · answered Aug 20 '19 at 08:13

Perhaps a slightly different approach where a sub query establishes how many qualifying fodex there are and the main query checks them. Note that pumpkin shares a fodex with some qualifying lns but gets a value of 0 as does 1722 because there is only 1 ln

drop table if exists t;
create table t
(LN  varchar(20), SEQNO int,  FODEX  int);
insert into t values
 ('TRES'      , 71842       ,    13    ),
 ('NOVOLLL'   , 44340       ,    13    ),
 ('NOVOLLL'   , 44340       ,   1059   ),
 ('TRES'      , 71842       ,   1059   ),
 ('TRES'      , 71842       ,   1317   ),
 ('NOVOLLL'   , 44340       ,   1317   ),
 ('ENBREL'    , 40869       ,   1722   ),
 ('ENBREL'    , 40869       ,   29359 ),
 ('tres'    , 40869       ,     29359 ),
 ('pumpkin'    , 40869       ,  29359)  ;


select ln,seqno,cntin,t.fodex,
        case when ln in ('tres','novolll','enbrel') and cntin > 1 then 1 
            when ln not in ('tres','novolll','enbrel') then 0
            when cntin <= 1 then 0
        end as 'y/n'
from t
join
(select fodex, sum(case when ln in ('tres','novolll','enbrel') then 1 else 0 end) as cntin 
    from t 
    group by fodex) s
on t.fodex = s.fodex
order by t.fodex
;

+---------+-------+-------+-------+------+
| ln      | seqno | cntin | fodex | y/n  |
+---------+-------+-------+-------+------+
| TRES    | 71842 |     2 |    13 |    1 |
| NOVOLLL | 44340 |     2 |    13 |    1 |
| NOVOLLL | 44340 |     2 |  1059 |    1 |
| TRES    | 71842 |     2 |  1059 |    1 |
| NOVOLLL | 44340 |     2 |  1317 |    1 |
| TRES    | 71842 |     2 |  1317 |    1 |
| ENBREL  | 40869 |     1 |  1722 |    0 |
| ENBREL  | 40869 |     2 | 29359 |    1 |
| tres    | 40869 |     2 | 29359 |    1 |
| pumpkin | 40869 |     2 | 29359 |    0 |
+---------+-------+-------+-------+------+
10 rows in set (0.00 sec)

SQL, query that finds if two given names have the same number in one column

2 Answers2