Ich erstelle eine Select-Anweisung, die zwei Tabellen kombiniert,zone undoutput, basierend auf einem referenziertendevice Tabelle und auf einer Zuordnung vonzone_number zuoutput_type_id. Die Zuordnung vonzone_number zuoutput_type_id wird nirgendwo in der Datenbank angezeigt, und ich möchte es "on-the-fly" in der select-Anweisung erstellen. Unten ist mein Schema:

CREATE TABLE output_type (
    id INTEGER NOT NULL, 
    name TEXT,
    PRIMARY KEY (id)
);

CREATE TABLE device (
    id INTEGER NOT NULL,
    name TEXT,
    PRIMARY KEY (id)
);

CREATE TABLE zone (
    id SERIAL NOT NULL,
    device_id INTEGER NOT NULL REFERENCES device(id),
    zone_number INTEGER NOT NULL,
    PRIMARY KEY (id), 
    UNIQUE (zone_number)
);

CREATE TABLE output (
    id SERIAL NOT NULL,
    device_id INTEGER NOT NULL REFERENCES device(id),
    output_type_id INTEGER NOT NULL REFERENCES output_type(id),
    enabled BOOLEAN NOT NULL,
    PRIMARY KEY (id)
);

Und hier sind einige Beispieldaten:

INSERT INTO output_type (id, name) VALUES 
(101, 'Output 1'),
(202, 'Output 2'),
(303, 'Output 3'),
(404, 'Output 4');

INSERT INTO device (id, name) VALUES 
(1, 'Test Device');

INSERT INTO zone (device_id, zone_number) VALUES 
(1, 1),
(1, 2),
(1, 3),
(1, 4);

INSERT INTO output (device_id, output_type_id, enabled) VALUES 
(1, 101, TRUE),
(1, 202, FALSE),
(1, 303, FALSE), 
(1, 404, TRUE);

Ich muss den dazugehörigen bekommenenabled Feld aus der Ausgabetabelle für jede Zone für ein bestimmtes Gerät. Jederzone_number Karten zu einemoutput_type_id. Für dieses Beispiel:

zone_number | output_type_id
----------------------------
1           | 101
2           | 202
3           | 303 
4           | 404

Eine Möglichkeit, mit der Zuordnung umzugehen, besteht darin, eine neue Tabelle zu erstellen

CREATE TABLE zone_output_type_map (
    zone_number INTEGER,
    output_type_id INTEGER NOT NULL REFERENCES output_type(id)
);

INSERT INTO zone_output_type_map (zone_number, output_type_id) VALUES 
(1, 101),
(2, 202),
(3, 303), 
(4, 404);

Verwenden Sie die folgende SQL-Anweisung, um alle Zonen abzurufen, sowie dieenabled Flagge, für Gerät 1:

SELECT zone.*, output.enabled 
FROM zone
JOIN output 
ON output.device_id = zone.device_id
JOIN zone_output_type_map map
ON map.zone_number = zone.zone_number
AND map.output_type_id = output.output_type_id
AND zone.device_id = 1

Ich suche jedoch nach einer Möglichkeit, die Zuordnung von Zonennummern zu Ausgabetypen zu erstellen, ohne eine neue Tabelle zu erstellen und ohne eine Reihe von AND / OR-Anweisungen zusammenzusetzen. Gibt es eine elegante Möglichkeit, eine Zuordnung zwischen den beiden Feldern in der select-Anweisung zu erstellen? So etwas wie:

SELECT zone.*, output.enabled 
FROM zone
JOIN output 
ON output.device_id = zone.device_id
JOIN (
    SELECT (
        1 => 101,
        2 => 202,
        3 => 303,
        4 => 404
    ) (zone_number, output_type_id)
) as map
ON map.zone_number = zone.zone_number
AND map.output_type_id = output.output_type_id
AND zone.device_id = 1

Haftungsausschluss: Ich kenne das im Idealfallenabled Feld würde in der existierenzone Tabelle. Ich habe jedoch keine Kontrolle über dieses Stück. Ich bin nur auf der Suche nach der elegantesten Lösung von der Anwendungsseite. Vielen Dank!