CODEWITHSUNDEEP
sql
dgoodwin
dgoodwin

Reputation: 21

Selecting unique identifiers with specific values

I know the title was not very clear so I do apologise - but I need some help.

I have got the table of the data below:

   GROUP_ID             CONTACT_ID           MAIN_CONTACT_FLAG
    11000                 28789                     0
    11000                 28049                     1
    21890                 10086                     0
    21890                 10554                     0
    21489                 14785                     0
    21464                 12457                     1
    21654                 14558                     0

I need to filter out any group_id's that do not have a contact_id that has the main_contact_flag set to 1

So the desired output would be:

   GROUP_ID             CONTACT_ID           MAIN_CONTACT_FLAG
    21890                 10086                     0
    21890                 10554                     0
    21489                 14785                     0
    21654                 14558                     0

I have tried the below queries but none of them give me the results I need:

SELECT GROUP_ID,MAIN_CONTACT_FLAG FROM Contact_Group_Contacts_T 
WHERE MAIN_CONTACT_FLAG != 0 AND MAIN_CONTACT_FLAG != 1
ORDER BY Group_ID DESC

SELECT DISTINCT GROUP_ID,Main_Group_Contact_BT 
FROM Contact_Group_Contacts_T 
WHERE NOT EXISTS (SELECT * FROM Contact_Group_Contacts_T WHERE MAIN_CONTACT_FLAG IN (0,1))

Thanks in advance, Dan

Upvotes: 0

Views: 45

Answers (5)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

Thee are many ways.

Your NOT EXISTS variant would work in most RDBMS, with this addition:

SELECT DISTINCT GROUP_ID
FROM   Contact_Group_Contacts_T T1
WHERE  NOT EXISTS (
   SELECT * FROM Contact_Group_Contacts_T T2
   WHERE  T2.GROUP_ID = T1.GROUP_ID  -- the missing piece!
   AND    T2.MAIN_CONTACT_FLAG = 1);

In PostgreSQL, assuming MAIN_CONTACT_FLAG is boolean NOT NULL, there is a simple way with the aggregate function bool_or()

SELECT group_id
FROM   contact_group_contacts_t
GROUP  BY 1
HAVING NOT bool_or(main_contact_flag);

Upvotes: 1

StanislavL
StanislavL

Reputation: 57381

SELECT * FROM Contact_Group_Contacts_T t INNER JOIN
    (SELECT GROUP_ID 
    FROM Contact_Group_Contacts_T 
    GROUP BY GROUP_ID
    HAVING SUM(MAIN_CONTACT_FLAG)=0) sub ON t.GROUP_ID=sub.GROUP_ID

Or if you need just group ids

    SELECT GROUP_ID 
    FROM Contact_Group_Contacts_T 
    GROUP BY GROUP_ID
    HAVING SUM(MAIN_CONTACT_FLAG)=0

UPDATE:

You need to case boolean (bit) to number to be summed

Use SUM(CASE WHEN MAIN_CONTACT_FLAG=0 THEN 0 ELSE 1 END)

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: you can achieve desired output by using subquery

select * 
from #tmp
where group_id not in (select distinct group_id from #tmp where main_contact_flag = 1)

OR NOT EXISTS

select t.* 
from #tmp t
where not exists (select 1 
                  from #tmp t1 where t.group_id = t1.group_id and t1.main_contact_flag = 1)

Upvotes: 0

Utsav
Utsav

Reputation: 8093

You need this

SQLFiddle

select * from table1 t1
where not exists
(select 1 from table1 t2
 where t1.group_id=t2.group_id
 and t2.MAIN_CONTACT_FLAG = 1)

Upvotes: 0

Satyadev
Satyadev

Reputation: 643

Try this :

select * from 
(
    select *,max(MAIN_CONTACT_FLAG) over (partition by GROUP_ID) as final_flag
    from tablename
)
where final_flag=0;

Upvotes: 0

Related Questions