Tukukkk

I have three tables: intrusion, alarms and CCTVs and I would like to use a case statement to obtain the registration_number of the object and its location, depending on whether it was a CCTV or an alarm that triggered an intrusion event in the intrusion table.

This is the intrusion table:

This is the alarm table:

The CCTV table is similar to the alarm table and this is my code:

SELECT

    ALARM_ID

,   CCTV_ID

,   CASE

        WHEN

            ALARM_ID    IS  NULL

        AND CCTV_ID IS  NOT NULL

        THEN

            (

                SELECT

                    REGISTRATION_NUMBER, LOCATION

                FROM

                    REMOTE_SECURITY.ALARMS

                WHERE

                    REMOTE_SECURITY.INTRUSIONS.ALARM_ID =   REMOTE_SECURITY.ALARMS.ALARM_ID

            )

        WHEN

            ALARM_ID    IS  NOT NULL

        AND CCTV_ID IS  NULL

        THEN

            (

                SELECT

                    REGISTRATION_NUMBER, LOCATION

                FROM

                    REMOTE_SECURITY.CCTVS

                WHERE

                    REMOTE_SECURITY.INTRUSIONS.cctv_id  =   REMOTE_SECURITY.CCTVS.CCTV_ID

            )

        ELSE

            'not running'

    END

FROM

    REMOTE_SECURITY.INTRUSIONS

I would like the final output to be a table with 2 columns: Location and Registration_number. (I dont need to know whether its a cctv or an alarm)

Any help would be greatly appreciated!

then why not just do:

SELECT location, registration_number FROM alarms

  WHERE alarm_id IN (SELECT alarm_id FROM intrusions)

UNION

SELECT location, registration_number FROM cctvs

  WHERE cctv_id IN (SELECT cctv_id FROM intrusions)

this should be mostly equivalent to your code, just much more terse and closer to your stated intent

if you do intend to get information from the intrusions table, then maybe something like:

SELECT intrusion_id, datetime_occurred,

  COALESCE(a.location, c.location) AS location,

  COALESCE(a.registration_number, c.registration_number) AS registration_number

FROM intrusions i

  LEFT JOIN alarms a ON i.alarm_id = a.alarm_id

  LEFT JOIN cctvs c ON i.cctv_id = c.cctv_id

not sure what you're doing to get a syntax error, it works for me after I do:

create temp table intrusions (intrusion_id serial primary key, datetime_occurred timestamp, alarm_id int, cctv_id int);

create temp table alarms (alarm_id serial primary key, location text, registration_number text);

create temp table cctvs (cctv_id serial primary key, location text, registration_number text);

It helps if you include this sort of code in the question! You'll get many more answers if you make it easier for other people…