What's the best way to compare the results of 2 Subselects on the same table?-CodePudding

Is there a better way to compare the result of 2 Sub-Selects on the same Table, than using 2 separate Queries?

In the following Query, I'd like to select all incidents where the "client" of the "creator" is not equal to the "client" of another user which I pass later on.

SELECT
    *
FROM
    incident i
WHERE
    i.client_id = 150
AND ( --can this AND be shortend?
    SELECT
        ur1.CLIENT_ID
    FROM
        USER ur1
    WHERE
        ur1.USER = upper(i.CREATOR)
) != (
    SELECT
        ur2.CLIENT_ID
    FROM
        USER ur2
    WHERE
        ur2.USER = upper('other')
)

Minimal reproducable example

Users inside the USER-Table are always Uppercase
Every User is unique
1 User can only have 1 Client_Id

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=99ac066a9abd339cd9a80a5b78716138

CodePudding user response：

If you have the constraints:

ALTER TABLE "USER" ADD CONSTRAINT
  user__id_user__pk PRIMARY KEY ("USER");

ALTER TABLE "USER" ADD CONSTRAINT
  user__id_user__u UNIQUE (client_id, "USER");

ALTER TABLE incident ADD CONSTRAINT
  incident__id_creator__fk FOREIGN KEY (client_id, creator)
  REFERENCES "USER" (client_id, "USER");

Then you can use:

SELECT *
FROM   incident i
WHERE  i.client_id = 150
AND    NOT EXISTS (
         SELECT u.client_id
         FROM   "USER" u
         WHERE  u."USER"    = upper('joe')
         AND    u.client_id = i.client_id
      )

If you do not have the foreign key constraint (and just have the unique/PK constraints on the USER table) then:

SELECT *
FROM   incident i
WHERE  i.client_id = 150
AND    EXISTS (
         SELECT 1
         FROM   "USER" u
         WHERE  u."USER"    IN (i.creator, upper('joe'))
         HAVING COUNT(DISTINCT client_id) > 1
      )

db<>fiddle here

CodePudding user response：

You can rewrite is as an exist query; in the subquery you select both records then see if client ids are different:

SELECT *
FROM incident i
WHERE i.client_id = 150
AND EXISTS (
    SELECT 1
    FROM USER
    WHERE USER IN (
        upper(i.CREATOR),
        upper('other')
    )
    HAVING COUNT(DISTINCT CLIENT_ID) = 2
)

CodePudding user response：

Maybe self join? Something like this:

SELECT i.*
  FROM incident i
       JOIN USER u1 ON u1.USER = i.creator
       JOIN USER u2 ON u2.client_id <> u1.client_id
 WHERE     i.client_id = 150
       AND u2.USER = 'OTHER'

CodePudding user response：

I think you can do it this way,

SELECT i.*
 FROM incident i, user ur1, user ur2
WHERE i.client_id = 150
  AND ur1.user =  UPPER(i.creator)
  AND ur2.user = UPPER(‘other’)
  AND ur1.client_id != ur2.client_id