CODEWITHSUNDEEP
sqloracle-database
blvck1337
blvck1337

Reputation: 3

Oracle SQL: One query two tables

I have the following base query:

SELECT INIT,TIDSPUNKT,AKTIVITETSTYPE 
   FROM ExampleDB.SAFE_DATA_VP_KR 
   WHERE DATO = '2020-05-25' AND
         AKTIVITETSTYPE = 'Udg¿ende/Produktion' AND
         INIT IN (SELECT initialer
                    FROM ExampleDB.SAFE_DATA_MEDARBEJDER
                    WHERE STATUS IN (1) AND
                          MED_STEDKODE_ID IN (5530) AND
                          ROLE_ID NOT IN (4)) 
   ORDER BY INIT, TIDSPUNKT;

I want to modify it and add a ACTIVITY_ID != 53 like the following, but it wont work.

SELECT INIT, TIDSPUNKT,AKTIVITETSTYPE 
  FROM ExampleDB.SAFE_DATA_VP_KR 
  WHERE DATO = '2020-05-25' 
  AND AKTIVITETSTYPE = 'Udg¿ende/Produktion' AND
      INIT IN (SELECT initialer 
                 FROM ExampleDB.SAFE_DATA_MEDARBEJDER 
                 WHERE STATUS IN (1) AND
                       MED_STEDKODE_ID IN (5530) AND
                       ROLE_ID NOT IN (4)) AND
      INIT IN (SELECT initialer 
                 FROM ExampleDB.SAFE_DATA_VAGTPLAN 
                 WHERE AKTIVITET_ID != 53) 
  ORDER BY INIT, TIDSPUNKT;

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Presumably, you want NOT IN to filter out any init values that have the 53 activity:

and init not in (Select initialer
                 from ExampleDB.SAFE_DATA_VAGTPLAN
                 where AKTIVITET_ID = 53
                )

Note: I strongly discourage the use of NOT IN with subqueries because they do not handle NULL values correctly. You should rewrite this using NOT EXISTS -- after you have given your tables references appropriate aliases and qualified all column references.

Upvotes: 1

Related Questions