Home » RDBMS Server » Server Administration » Help!
Help! [message #373702] Wed, 02 May 2001 07:29 Go to next message
swati
Messages: 7
Registered: May 2001
Junior Member
Dear Sir/Madam,

my table structure is :

q_id varchar()
ques_ans varchar()
reply_id varchar()
name varchar()
category_id varchar()

in the above table q_id contains question or answer id
If answer of a perticular question is exist then q_id will go to reply_id()

I have written a self-join query which is as below:-

select distinct b.ques_ans,a.ques_ans,b.name from dfm_ques_ans_master a,dfm_ques_ans_master b where a.replyto_id=b.q_id and a.category_id='03';

it displays result like :-

What is application?
abcdjasf

What is application?
cxygkgfdg

Here there are two different answers for the same question. I want question should be displayed only once with the following answers

would you help me?

Thanx

Swati
Re: Help! [message #373705 is a reply to message #373702] Wed, 02 May 2001 07:53 Go to previous messageGo to next message
siddharth
Messages: 16
Registered: May 2001
Junior Member
Hi Swati,

select ques_ans,q_id quesid from dfm_ques_ans_master where a.category_id='03'
UNION
select ques_ans,name from dfm_ques_ans_master where replyto_id = quesid

See If This works.

regds.
Siddharth
Re: Help! [message #373725 is a reply to message #373705] Thu, 03 May 2001 00:03 Go to previous messageGo to next message
swati
Messages: 7
Registered: May 2001
Junior Member
Hi Siddharth,

Thanks for replying my query..

I have tried with

select ques_ans,name from dfm_ques_ans_master where category_id='03'
UNION
select ques_ans,name from dfm_ques_ans_master where replyto_id=qa_id

which is giving output as

djkfjdkdfjk
adsjfjadsf
what is your name?

dasfj
where do you stay?

You can see that it is showing answer first then question but actually it should be vice a versa

could you help me...

with regards

swati
Re: Help! [message #373726 is a reply to message #373705] Thu, 03 May 2001 01:27 Go to previous messageGo to next message
siddharth
Messages: 16
Registered: May 2001
Junior Member
Hi Swati,

If the qa_id for questions is always smaller than the qa_id of the answer (Answer is entered in the database only after the question is entered.) you can order by qa_id

select ques_ans,name,qa_id as quesid from dfm_ques_ans_master where category_id='03'
UNION
select ques_ans,name,qa_id as quesid from dfm_ques_ans_master where replyto_id=quesid order by quesid asc.

Otherwise you can try another workaround

select ques_ans,name,1 as orderno
from dfm_ques_ans_master where category_id='03'
UNION
select ques_ans,name,2 as orderno from dfm_ques_ans_master where replyto_id=quesid order by orderno asc.

HTH,
Siddharth
Re: Help! [message #373740 is a reply to message #373702] Thu, 03 May 2001 15:27 Go to previous message
Sundar Venkatasubramaniam
Messages: 26
Registered: May 2001
Junior Member
Could you please explain in more detail with examples what is purpose of each field,so that it will be helpful in finding the solution?
Previous Topic: No of days depts were vacant
Next Topic: Order by
Goto Forum:
  


Current Time: Mon Dec 23 10:23:46 CST 2024