Posted by --CELKO-- on 03/06/06 17:54

Do not use assembly language style bit flags in a high level language
like SQL. Use a sequence number for zones, if the zone-id will not do
the job. All relationships have to be expressed as values in columns
in tables. You never refer to the physical storage in a quiery.

CREATE TABLE Zones
(zone_id INTEGER NOT NULL PRIMARY KEY
zone_name CHAR(10) NOT NULL,
zone_rank INTEGER DEFAULT 0 NOT NULL
CHECK (zone_rank > 0),
UNIQUE (zone_id, zone_rank))
);


CREATE TABLE ZoneUsers
(user_id_1 INTEGER NOT NULL,
user_id_2 INTEGER NOT NULL,
PRIMARY KEY (user_id_1, user_id_2),
zone_id INTEGER NOT NULL
REFERENCES Zones(zone_id)
);

SELECT U.user_id_1, U.user_id_2, U.zone_id, MIN(Z.zone_rank)
FROM ZoneUsers AS U, Zones AS Z
WHERE Z.zone_id = U.zone_id
GROUP BY U.user_id_1, U.user_id_2, U.zone_id;

[Back to original message]