How to look up in SQL-CodePudding

I have a table named PERSON . Each person has a person ID and has an ADDITIONAL INFO table with 5 entries of types of contracts - C1 through C5 . Each contract will have a unique name.if person has not accepted a contract,then that contract will not show up in his ADDITIONAL INFO table.Eg:Person 1 has ADDITIONAL INFO Table with only 3 raws - C2-BLOWSY,C4-GEN,C5-OLJ . This means Person 1 has not accepted contracts C1 and C3.

I need the list of Persons who haven't accepted contract C1.

Problem is that i cannot put the condition as "Where C1 = NULL" since Raw C1 itself will not appear if person has not accepted that contract.Logic is that if ADDITIONAL INFO table don't have entry named C1,Then we can include this person in the list.But i don't know how to write query for this.

Please help .Thanks in advance

CodePudding user response：

Assuming that table of contracts exist we have:

WITH PERSON AS    (
                   select 1 as id_person, 'John Doe 1' as name_person from dual union all  
                   select 2 as id_person, 'John Doe 2' as name_person from dual
                  )
    ,ADD_INFO AS  (
                   select 1 as id_person, 'C2-BLOWSY' as type_contract from dual union all
                   select 1 as id_person, 'C4-GEN'    as type_contract from dual union all
                   select 1 as id_person, 'C5-OLJ'    as type_contract from dual
                  )
    ,CONTRACTS AS (
                   select 'C1-IJK'    as type_contract from dual union all
                   select 'C2-BLOWSY' as type_contract from dual union all 
                   select 'C3-ADF'    as type_contract from dual union all
                   select 'C4-GEN'    as type_contract from dual union all
                   select 'C5-OLJ'    as type_contract from dual
                  )
      select p.id_person, p.name_person, c.type_contract
        from person p, 
             contracts c
       where c.type_contract = 'C1-IJK'   --Search by C1 missed     
       group by  p.id_person, p.name_person, c.type_contract
       having (select count(*) from add_info a where a.id_person = p.id_person and a.type_contract = c.type_contract) = 0
       order by p.id_person, c.type_contract;

 ID_PERSON NAME_PERSO TYPE_CONT
---------- ---------- ---------
         1 John Doe 1 C1-IJK   
         2 John Doe 2 C1-IJK

Thank you

CodePudding user response：

Create person and additional info tables:

CREATE TABLE dbo.PERSON
    (
    PERSON_ID int NULL,
    PERSON_NAME varchar(50) NULL
    )  ON [PRIMARY]
GO

CREATE TABLE dbo.ADDL_INFO
    (
    PERSON_ID int NULL,
    CONTRACT varchar(10) NULL
    )  ON [PRIMARY]
GO

Insert the data to match your scenario:

INSERT INTO dbo.PERSON VALUES (1, 'Person1');
INSERT INTO dbo.ADDL_INFO VALUES (1, 'C1');
INSERT INTO dbo.ADDL_INFO VALUES (1, 'C2');
INSERT INTO dbo.ADDL_INFO VALUES (1, 'C3');
INSERT INTO dbo.ADDL_INFO VALUES (1, 'C4');
INSERT INTO dbo.ADDL_INFO VALUES (1, 'C5');

INSERT INTO dbo.PERSON VALUES (2, 'Person2');
INSERT INTO dbo.ADDL_INFO VALUES (2, 'C2');
INSERT INTO dbo.ADDL_INFO VALUES (2, 'C4');
INSERT INTO dbo.ADDL_INFO VALUES (2, 'C5');

The below query will select the people that DO NOT have contract 'C1'. Please note to make this reusable, the 'C1' hard-coded below should be replaced with a ? and this could be used as a parameterized query in your application:

SELECT PERSON_ID, PERSON_NAME FROM dbo.PERSON RSLT
WHERE NOT EXISTS (
    SELECT P.PERSON_ID FROM dbo.PERSON P
        INNER JOIN dbo.ADDL_INFO A ON A.PERSON_ID = P.PERSON_ID
    WHERE A.CONTRACT = 'C1' and RSLT.PERSON_ID = P.PERSON_ID
)

Results: