CODEWITHSUNDEEP
sqlsql-servergroup-by
Flemming Hald
Flemming Hald

Reputation: 83

How do I Group By on two fields without using Group By?

I have a message table containing messages, threads, from workgroup id, and to workgroup id. When from_wg_id is null I know the message is from a student. When it is not null I know the message is from an employee in a workgroup. When to_wg_id is null I know the message is a reply to a student and when from_wg_id is not null and to_wg_id is not null I know the message is a forward from one employee to another. Here is my challenge: I want to see all workgroups within each thread (for simplicity I am only showing one here: 19407) which have both a reply to a student and a forward to another employee. How do I do that? I've tried with group by but I keep getting CESADM in the output and since that only has a reply I don't want to show that.

Source table: Message_log

Message_ID  Thread_ID   FROM_WG_ID  TO_WG_ID
1           19407       CESADM      NULL
2           19407       NULL        SCHOLAR     
3           19407       NULL        SCHOLAR     
4           19407       SCHOLAR     NULL
5           19407       SCHOLAR     CESADM

Desired output table: Message_log

Thread_ID   FROM_WG_ID      
19407       SCHOLAR

How do I write a query which will generate the desired output table? (The reason for this output is that SCHOLAR is the only workgroup within thread 19407 which has both a forwarded and a replied messsage)

Upvotes: 0

Views: 22

Answers (1)

D Stanley
D Stanley

Reputation: 152556

You can use EXISTS to see if the a forward to another employee is found:

SELECT 
    Thread_ID,
    TO_WG_ID 
FROM Messages M1
WHERE FROM_WG_ID IS NULL
AND EXISTS(
   SELECT NULL FROM Messages M2
   WHERE M2.Thread_ID = M1.Thread_ID
     AND M2.FROM_WG_ID = M1.TO_WG_ID  
     AND TO_WG_ID IS NOT NULL)

Upvotes: 1

Related Questions