SELECT all or a few

26 Aug 2006 2:57 AM

J. M. De Moor

CREATE TABLE Limiter (
ref_code CHAR(1) NOT NULL PRIMARY KEY
,limit_it CHAR(1) NOT NULL
   CHECK (limit_it IN ('T', 'F'))
   DEFAULT 'F'
);

-- Limiter will have at most one entry in it at a time
INSERT INTO Limter VALUES ('A', 'T');

CREATE TABLE Stuff (
item_code CHAR(1) NOT NULL PRIMARY KEY
,ref_code CHAR(1) NOT NULL
);

INSERT INTO Stuff VALUES ('1', 'A');
INSERT INTO Stuff VALUES ('2', 'B');
INSERT INTO Stuff VALUES ('3', 'C');
INSERT INTO Stuff VALUES ('4', 'A');
INSERT INTO Stuff VALUES ('5', 'C');

Although both ref_code columns draw from the same domain, there is no
foreign key relationship between these 2 tables.    In the above example,
the resultset should be all item_code values in Stuff table with ref_code =
'A' because limit_it for ref_code 'A' is set to 'T'.

item_code
----------
1
4

However, if the entry in Limter is like this

INSERT INTO Limter VALUES ('A', 'F');

then the resultset should include everything (i.e., NOT be limited to the
ref_code in Limter):

item_code
-----------
1
2
3
4
5

Got query? Thanks...

Joe

26 Aug 2006 3:50 AM

Stu

Not sure how why this design is what you want; it seems a bit
unorthodox, but the following query worked for me:

SELECT Stuff.item_code, Stuff.ref_code
FROM Stuff JOIN Limiter ON (CASE WHEN Limiter.limit_it = 'T' THEN
Stuff.ref_code ELSE limiter.ref_code END) = Limiter.ref_code;

Of course, if Limiter has more than one row, you'll get a cross join.

Stu

J. M. De Moor wrote:
Show quote

> CREATE TABLE Limiter (
>   ref_code CHAR(1) NOT NULL PRIMARY KEY
> ,limit_it CHAR(1) NOT NULL
>    CHECK (limit_it IN ('T', 'F'))
>    DEFAULT 'F'
> );
>
> -- Limiter will have at most one entry in it at a time
> INSERT INTO Limter VALUES ('A', 'T');
>
> CREATE TABLE Stuff (
>   item_code CHAR(1) NOT NULL PRIMARY KEY
> ,ref_code CHAR(1) NOT NULL
> );
>
> INSERT INTO Stuff VALUES ('1', 'A');
> INSERT INTO Stuff VALUES ('2', 'B');
> INSERT INTO Stuff VALUES ('3', 'C');
> INSERT INTO Stuff VALUES ('4', 'A');
> INSERT INTO Stuff VALUES ('5', 'C');
>
> Although both ref_code columns draw from the same domain, there is no
> foreign key relationship between these 2 tables.    In the above example,
> the resultset should be all item_code values in Stuff table with ref_code =
> 'A' because limit_it for ref_code 'A' is set to 'T'.
>
> item_code
> ----------
> 1
> 4
>
> However, if the entry in Limter is like this
>
> INSERT INTO Limter VALUES ('A', 'F');
>
> then the resultset should include everything (i.e., NOT be limited to the
> ref_code in Limter):
>
> item_code
> -----------
> 1
> 2
> 3
> 4
> 5
>
> Got query? Thanks...
>
> Joe

26 Aug 2006 7:27 AM

J. M. De Moor

Thanks, Stu. Didn't know you could use CASE that way.

> Not sure how why this design is what you want; it seems a bit
> unorthodox, but the following query worked for me:
>
>
> SELECT Stuff.item_code, Stuff.ref_code
> FROM Stuff JOIN Limiter ON (CASE WHEN Limiter.limit_it = 'T' THEN
> Stuff.ref_code ELSE limiter.ref_code END) = Limiter.ref_code;
>
> Of course, if Limiter has more than one row, you'll get a cross join.
>

Yeah, it doesn't make sense with this little info. Actually, Limiter is a
view that returns a single row. I really dumbed this down, but it gives the
essense of what I am attempting in the query.

Joe