February 3, 2003 at 5:23 am
CREATE TABLE [route_detail] (
[route_number] [int] NOT NULL ,
[question_set_head] [varchar] (2) ,
[question_set_tail] [int] NOT NULL ,
[question_no] [int] NOT NULL ,
[time_limit_question] [int] NOT NULL
) GO
CREATE TABLE [question_bank] (
[question_set_head] [nvarchar] (2) not NULL ,
[question_set_tail] [int] NOT NULL ,
[question_no] [int] NOT NULL ,
[frame_file] [nvarchar] (60) NOT NULL ,
[no_of_options] [int] NOT NULL
) GO
the thing is one questionset head can have multiple questionsettail and that questinset tail can have multiple questions
depending upon the route number from the route_detail table so i want to display only
two records from the question_bank where the route number =4 and mething the creteria of equality checking from the route detail on
question_bank.question_set_head = route_detail.question_set_head AND
question_bank.question_set_tail = route_detail.question_set_tail
AND question_bank.question_no = route_detail.question_no
WHERE route_detail.route_number = 4
Please help me out as soon as possible
vijay verma
Edited by - vijaysang123 on 02/03/2003 05:29:24 AM
vijay verma
February 3, 2003 at 11:24 am
select TOP 2 b.question_set_head,
b.question_set_tail,
b.question_no,
b.frame_file,
b.no_of_options
from route_detail d
inner join question_bank
ON b.question_set_head = d.question_set_head
AND b.question_set_tail = d.question_set_tail
AND b.question_no = d.question_no
WHERE d.route_number = 4
If you want a random selection then that will involve a bit more.
Far away is close at hand in the images of elsewhere.
Anon.
February 3, 2003 at 10:36 pm
Thanks for your reply but it diaply only two records which is not my requirement.
what i want is that i should get 2 records of each question set tail where as in your case it display only two records. the thing is that every question_set_tail field will be having more than five to six records so i want to disply two records from each question_set_tail of the question_set_head field pls if you can give me reply as soon as possible.
Thanks.
vijay verma
vijay verma
February 3, 2003 at 10:36 pm
Thanks for your reply but it diaply only two records which is not my requirement.
what i want is that i should get 2 records of each question set tail where as in your case it display only two records. the thing is that every question_set_tail field will be having more than five to six records so i want to disply two records from each question_set_tail of the question_set_head field pls if you can give me reply as soon as possible.
Thanks.
vijay verma
vijay verma
February 4, 2003 at 2:24 am
try
select b.question_set_head,
b.question_set_tail,
b.question_no,
b.frame_file,
b.no_of_options
from route_detail d
inner join question_bank b
ON b.question_set_head = d.question_set_head
AND b.question_set_tail = d.question_set_tail
AND b.question_no = d.question_no
WHERE d.route_number = 4
and (select count(*) from route_detail where question_set_head = b.question_set_head and question_set_tail = b.question_set_tail and question_no <= b.question_no) <= 2
Edited by - davidburrows on 02/04/2003 02:27:05 AM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply