November 5, 2003 at 10:19 am
Hi Can anyone help me with the following problem. I want to return only one instance of column QT_ID and ANS_ID, Or better still, the column create as ANSID )how can I do this using the DISTINCT keyword ?, at the moment Im getting duplicate rows.
select Q.QT_ID, AT.ANS_ID,
(Q.QT_ID + nvl(AT.ANS_ID,0)) AS ANSID,
Q.QT_PQ_NUM,
Q.QT_PQ_CAT,
Q.QT_ANS_DUE_DATE,
Q.QT_DATE_PQ_DUE,
Q.QT_HOLDING_REP,
QT.QT_PQ_TEXT,
A.ANS_DATE,
A.ANS_HANSARD_REF,
A.ANS_APPROVED_DATE,
AT.ANS_TEXT,
M.MIN_DESC,
ME.MEM_DESC,
ME.MEM_FIRST_NAME,
ME.MEM_CONSTITUENCY,
ME.TITLE
from QA_LOGS QL,
QUESTIONS Q,
QT_TEXT QT,
ANSWERS A,
ANSWER_TEXT AT,
MINISTER M,
MEMBER ME,
QT_ANS QTA
where QL.LOGIN = 'IMP337'
and QL.QUESTION = Q.QT_ID
and QL.QUESTION = QT.QT_ID
and Q.QT_ID = QTA.QT_ID (+)
and QTA.ANS_ID = A.ANS_ID (+)
and QTA.ANS_ID = AT.ANS_ID (+)
and Q.MIN_ID = M.MIN_ID (+)
and Q.MEM_ID = ME.MEM_ID
order by Q.QT_ID,AT.ANS_ID
November 10, 2003 at 12:00 pm
This was removed by the editor as SPAM
November 10, 2003 at 5:13 pm
write a subquery which will return only qt_id and Ans_Id combination and then join that subquery with rest of your conditions and select statements.
like this...
select subquery.qt_id, subquery.ans_id, <<rest of the columns>> from (select distinct qt_id, ans_id from blah blah JOIN blah blah), <<rest of the tables and conditions>>
Linto
November 11, 2003 at 6:50 am
Duplicate data can be caused by more than 1 matching row in multiple tables or by insufficient matching of tables. You need to check what is duplicated and do as linto suggests.
May be useful to use SQL-92 compliant joins which would make possible problems easier to see and also posting small example of data may help.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply