Newbie with queries

[Narita Joardar]

I need the message ids and endpoints of messages from whom endpoints in multicast_message_recovery_processed is not in the list of endpoints of the multicast_endpoints table, for a given prcs_id, prcs_run_id, multicast_id.

Data -

multicast_endpoints

prcs_id prcs_run_id multicast_id endpoint_id

    100     1           multiX       destA

    100     1           multiX       destB

    100     1           multiX       destC

    100     1           multiX       destD

    100     1           multiY       destX

    100     1           multiY       destY

multicast_message_recovery_processed

 prcs_id prcs_run_id multicast_id message_id endpoint_id

    100     1           multiX       1          destA

    100     1           multiX       2          destB

    100     1           multiX       1          destC

    100     1           multiY       1          destX

For a given `prcs_id = 100, prcs_run_id = 1, multicast_id = multiX`, the query should return:

1 destB
1 destD
2 destA
2 destC
2 destD
This essentially means that message 1 has not reached `destB` and `destD` while message 2 has not reached `destA`, `destC` and `destD`.

 CREATE TABLE multicast_message_recovery_processed
        (
          prcs_id character varying NOT NULL,
          prcs_run_id bigint NOT NULL,
          multicast_id character varying NOT NULL,
          message_id bigint,
          endpoint_id character varying NOT NULL,
          CONSTRAINT multicast_message_recovery_processed_pkey PRIMARY KEY (prcs_id , prcs_run_id , multicast_id , endpoint_id )
        )
    
        CREATE TABLE multicast_endpoints
        (
          prcs_id character varying NOT NULL,
          prcs_run_id bigint NOT NULL,
          multicast_id character varying NOT NULL,
          endpoint_id character varying NOT NULL,
          CONSTRAINT multicast_endpoints_pkey PRIMARY KEY (prcs_id , prcs_run_id , multicast_id , endpoint_id )
        )

[Ireneusz Kordal]

Try this query.
I shortended name of multicast_message_recovery_processed table since my database accepts only names up to 30 characters.

SELECT UNIQUE mmr.prcs_id, 
              mmr.prcs_run_id, 
              mmr.multicast_id, 
              mmr.message_id, 
              me.endpoint_id
FROM multicast_endpoints me
JOIN multicast_message_recovery mmr
ON ( me.prcs_id = mmr.prcs_id
     AND me.prcs_run_id  = mmr.prcs_run_id
     AND me.multicast_id = mmr.multicast_id
)
WHERE NOT EXISTS (
  SELECT 1 FROM multicast_message_recovery x1
  WHERE 
    x1.prcs_id = me.prcs_id
    AND x1.prcs_run_id = mmr.prcs_run_id
    AND x1.multicast_id = mmr.multicast_id
    AND x1.message_id = mmr.message_id
    AND x1.endpoint_id = me.endpoint_id
)
ORDER BY  1,2,3,4,5
;


PRCS_ID	PRCS_RUN_ID	MULTICAST_ID	MESSAGE_ID	ENDPOINT_ID
    100	        1  	     multiX		1           destB
    100	        1  	     multiX		1   	    destD
    100	        1  	     multiX		2           destA
    100	        1  	     multiX		2   	    destC
    100	        1  	     multiX		2           destD
    100	        1  	     multiY		1   	    destY

 6 rows selected 

[Campbell Ritchie]

. . . and welcome to the Ranch