Re: SQL question/puzzle
I agree with everything Kai said except this.
>I don't think there is a way to select the ranges in straight SQL.
I don't see any reason (except, perhaps, for inefficiency) why you
shouldn't use the following compound non-equijoin in your SELECT for the
Stuff_ID values:
SELECT R.Stuff_ID
FROM Relate R, Permit P
WHERE P.User_ID = <given-user_id>
AND R.Co_nbr >= P.Co_nbr_lo
AND R.Co_nbr <= P.Co_nbr_hi
If you want the data about the stuff, then you obviously use:
SELECT S.Stuff_ID, S.Data
FROM Stuff S, Relate R, Permit P
WHERE P.User_ID = <given-user_id>
AND R.Co_nbr >= P.Co_nbr_lo
AND R.Co_nbr <= P.Co_nbr_hi
AND S.Stuff_ID = R.Stuff_ID
See the example (5.00 or later) SQL script at the bottom...
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
>From: "Kai G. Hintze" <kaih@develop.utcourts.gov>
>Date: Wed, 8 Mar 95 11:02:43 MST
>X-Informix-List-Id: <list.5703>
>
>> Assume the following tables:
>>
>> STUFF (stuff_id, data....)
>> stuff_id - unique ID for this record/tuple.
>> data - the interesting part for a user.
>>
>> RELATE (stuff_id, co_nbr)
>> stuff_id - foreign key into STUFF
>> co_nbr - a valid company number for this STUFF record.
>>
>> STUFF one-to-many with RELATE. i.e, there can be several
>> company numbers associated with a given STUFF record. (1-5)
>>
>> PERMIT (user_id, co_nbr_lo, co_nbr_hi)
>> user_id - login ID for a user.
>> co_nbr_lo - lowest company number for this range
>> co_nbr_hi - highest company number for this range
>>
>> Finally, the question: Given a user_id, how to find all STUFF
>> records this user's company number ranges allows her access to?
>
>[ Lots of good answer deleted ... ]
CREATE TABLE Stuff
(
Stuff_ID INTEGER NOT NULL PRIMARY KEY CONSTRAINT Pk_Stuff,
Data CHAR(20) NOT NULL
);
INSERT INTO Stuff VALUES (1, "Data for item 1");
INSERT INTO Stuff VALUES (2, "Data for item 2");
INSERT INTO Stuff VALUES (3, "Data for item 3");
INSERT INTO Stuff VALUES (4, "Data for item 4");
INSERT INTO Stuff VALUES (5, "Data for item 5");
INSERT INTO Stuff VALUES (6, "Data for item 6");
INSERT INTO Stuff VALUES (7, "Data for item 7");
INSERT INTO Stuff VALUES (8, "Data for item 8");
INSERT INTO Stuff VALUES (9, "Data for item 9");
CREATE TABLE Relate
(
Stuff_ID INTEGER NOT NULL,
Co_nbr INTEGER NOT NULL,
PRIMARY KEY (Stuff_ID, Co_nbr) CONSTRAINT Pk_Relate
);
INSERT INTO Relate VALUES (1, 1);
INSERT INTO Relate VALUES (2, 2);
INSERT INTO Relate VALUES (3, 3);
INSERT INTO Relate VALUES (4, 4);
INSERT INTO Relate VALUES (1, 2);
INSERT INTO Relate VALUES (7, 2);
INSERT INTO Relate VALUES (8, 2);
INSERT INTO Relate VALUES (9, 2);
-- Errr, what is the correct primary key for this table?
CREATE TABLE Permit
(
User_ID INTEGER NOT NULL,
Co_nbr_lo INTEGER NOT NULL,
Co_nbr_hi INTEGER NOT NULL
);
INSERT INTO Permit VALUES (1, 2, 2);
INSERT INTO Permit VALUES (1, 4, 6);
INSERT INTO Permit VALUES (2, 2, 4);
SELECT S.Stuff_ID, S.Data
FROM Stuff S, Relate R, Permit P
WHERE P.User_ID = 1
AND R.Co_nbr >= P.Co_nbr_lo
AND R.Co_nbr <= P.Co_nbr_hi
AND S.Stuff_ID = R.Stuff_ID;
SELECT S.Stuff_ID, S.Data
FROM Stuff S, Relate R, Permit P
WHERE P.User_ID = 2
AND R.Co_nbr >= P.Co_nbr_lo
AND R.Co_nbr <= P.Co_nbr_hi
AND S.Stuff_ID = R.Stuff_ID;