CODEWITHSUNDEEP
sqlfirebird
Nukool
Nukool

Reputation: 25

Using multiple NOT IN in Firebird

Table: ShareNumber

ShareTypeCode ShareNumber StartDate LastDate
stc1 snA 2021-01-01 2021-01-31
stc1 sn001 2021-01-01 null
stc1 sn002 2021-01-01 null
stc1 sn003 2021-01-01 null
stc1 sn004 2021-01-01 null
stc1 sn005 2021-01-01 null
stc2 sn001 2021-01-01 null
stc2 sn002 2021-01-01 null
stc2 sn003 2021-01-01 null
stc2 sn004 2021-01-01 null
stc2 sn005 2021-01-01 null

Table: ShareHolding

ShareHolderCode ShareTypeCode ShareNumber StartDate LastDate
shc1 stc1 sn001 2021-01-01 2021-1-31
shc1 stc1 sn002 2021-01-01 null

Table: ShareNumber

ShareTypeCode stc1 with ShareNumber snA to sn005 was canceled. ShareTypeCode stc1 with ShareNumber sn001 to sn005 are valid share number. ShareTypeCode stc2 with ShareNumber sn001 to sn005 are valid share number.

Table: ShareHolding

ShareTypeCode stc1 with ShareNumber sn001 was sold to a shareholder and finally the shareholder exited and the company bought it back. ShareTypeCode stc1 with ShareNumber sn002 was sold to a shareholder and the shareholder is still holding it.

SQL for valid shares:

select "ShareTypeCode", "ShareNumber" 
from "ShareHolding" 
where "LastDate" is null

Can you please help correct the following SQL statement if an investor would like to buy a share, ShareTypeCode stc1 with ShareNumber sn002?

SELECT "SN"."ShareTypeCode", "SN"."ShareNumber" 
FROM "ShareNumber" "SN" 
WHERE "SN"."LastDate" is null 
and "SN"."ShareTypeCode" = 'stc1' 
and "SN"."ShareNumber" = 'sn002' 
and "SN"."ShareTypeCode", "SN"."ShareNumber" not in ( select "SH"."ShareTypeCode", "SH"."ShareNumber" from "ShareHolding" "SH" where "SH"."LastDate" is null)

If found
   ShareTypeCode stc1 with ShareNumber sn002 is available for sale.
End If

Upvotes: 0

Views: 335

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108991

Firebird doesn't support row values, and if it did, the SQL standard syntax requires them to be enclosed in parentheses. That is, your query would then be ("SN.ShareTypeCode", "SN.ShareNumber") not in (...). However, as Firebird doesn't support this, this is a moot point.

Instead, you need to use a correlated subquery with NOT EXISTS:

SELECT "SN"."ShareTypeCode", "SN"."ShareNumber" 
FROM "ShareNumber" "SN" 
WHERE "SN"."LastDate" is null 
and "SN"."ShareTypeCode" = 'stc1' 
and "SN"."ShareNumber" = 'sn002' 
and not exists (
  select *
  from "ShareHolding" "SH" 
  where "SH"."LastDate" is null
  and "SN"."ShareTypeCode" = "SH"."ShareTypeCode"
  and "SN"."ShareNumber" = "SH"."ShareNumber"
)

Upvotes: 2

Related Questions