List Of Input Compare With Ranges

[Durrani]

CREATE TABLE master (

masterid BIGINT NOT NULL,

name VARCHAR(25),

CONSTRAINT PK_master PRIMARY KEY (masterid)

);

commit;

CREATE TABLE detail (

masterid BIGINT NOT NULL,

rangestart BIGINT,

rangeend BIGINT,

CONSTRAINT FK_detail_master FOREIGN KEY (masterid) REFERENCES master (masterid)

);

commit;

INSERT INTO master(masterid, name) values(1, 'master1');

INSERT INTO master(masterid, name) values(2, 'master2');

INSERT INTO master(masterid, name) values(3, 'master3');

commit;

insert into detail(masterid, rangestart, rangeend) values(1,100, 200);

insert into detail(masterid, rangestart, rangeend) values(1,250,300);

insert into detail(masterid, rangestart, rangeend) values(1,350,400);

insert into detail(masterid, rangestart, rangeend) values(2,100,200);

insert into detail(masterid, rangestart, rangeend) values(2,320,350);

insert into detail(masterid, rangestart, rangeend) values(2,390,400);

insert into detail(masterid, rangestart, rangeend) values(3,100,200);

insert into detail(masterid, rangestart, rangeend) values(3,250,300);

insert into detail(masterid, rangestart, rangeend) values(3,355,455);

commit;

Input values is an array containing values:

case a:

120

For this input all three masterid should be returned, since 120 falls in range of all the three master ids.

case b:

120

260

For this input masterid 1 and 3 should be returned, since 260 does not fall in the ranges of masterid 2, that should not be returned.

case c:

110

260

350

In this case the query should return masterid 1 only.

FYI… I am using Firebird 2, and it supports ANSI.

[peterlaursen]

 Can you please explain how this is related to SQLyog?  Your are positng in a Froums where we discuss SQLyog source-code (what is C++ and not SQL!)

Anyway what are the select-queries referred to as case a|b|c? and what do you mean my 'Input values is an array …'? MySQL does not support arrays.

[Author]

Posts