Need help with SQL Select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need help with SQL Select statement

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2003-11-12 : 02:22:40

Im trying to use this statement:

SELECT Distinct D.DocumentID,D.FileName,D.Title,D.ShortDescription,D.DatePublished,DT.DocumentID,DT.DocumentType,DT.ThumbNailImage from Documents D, DocumentSubjectAreas DSA, DocumentSubjectAreasREL DSAR, DocumentTypes DT, DocumentTypesRel DTR WHERE Instr(Lcase(DSA.SubjectArea), 'new') > 0 AND D.DocumentID=DSAR.DocumentID And DSAR.SubjectAreaID=DSA.SubjectAreaID And D.DocumentID=DTR.DocumentID AND DT.DocumentTypeID=DTR.DocumentTypeID Order By D.DatePublished desc

The problem is that it doesnt return only distinct values for DocumentID. The table Documents, contains the DocumentID primary key column. The table DocumentTypesREL holds the relation between document types and documents. Therefore, a document may have multiple document types and therefore the DocumentID key will be found in DocumentTypesRel multiple times, however, I need to only select distinct DocumentID's from DocumentTypesRel. I cant seem to figure out why my statement isnt doing this. Can anyone help?

Thanks, lance

ljp099
Yak Posting Veteran

79 Posts

Posted - 2003-11-12 : 02:29:54

Correction, this is the statement im using:

SELECT Distinct D.DocumentID,D.FileName,D.Title,D.ShortDescription,D.DatePublished,DTR.DocumentID,DT.DocumentType,DT.ThumbNailImage from Documents D, DocumentSubjectAreas DSA, DocumentSubjectAreasREL DSAR, DocumentTypes DT, DocumentTypesRel DTR WHERE Instr(Lcase(DSA.SubjectArea), 'new') > 0 AND D.DocumentID=DSAR.DocumentID And DSAR.SubjectAreaID=DSA.SubjectAreaID And D.DocumentID=DTR.DocumentID AND DT.DocumentTypeID=DTR.DocumentTypeID Order By D.DatePublished desc

mr_mist
Grunnio

1870 Posts

Posted - 2003-11-12 : 03:24:38

It's not doing it because ypour distinct is across the entire select entry..

First off, try using the ANSI style joins so it's more obvious what is going on..

SELECT Distinct D.DocumentID,D.FileName,D.Title,D.ShortDescription,D.DatePublished,
DTR.DocumentID,DT.DocumentType,DT.ThumbNailImage from
Documents D

INNER JOIN
DocumentSubjectAreasREL DSAR
ON D.DocumentID=DSAR.DocumentID

INNER JOIN
DocumentSubjectAreas DSA
ON DSAR.SubjectAreaID=DSA.SubjectAreaID

INNER JOIN
DocumentTypesRel DTR
ON D.DocumentID=DTR.DocumentID

INNER JOIN
DocumentTypes DT
ON DT.DocumentTypeID=DTR.DocumentTypeID

WHERE Instr(Lcase(DSA.SubjectArea), 'new') > 0
Order By D.DatePublished desc

Then you'd need something like

INNER JOIN
(SELECT DISTINCT documentid from DocumentTypesRel ) distincttypes
ON distincttypes.documentid = d.documentid

to cut things down a little, but you would then have to use lots of
MINs and a GROUP BY to filter the output from the other tables.

What is the critertia that determines which row should be displayed in the
case of a duplicate?

-------
Moo. :)

ljp099
Yak Posting Veteran

79 Posts

Posted - 2003-11-12 : 03:53:49

thanks for the help.

the criteria that determines which row to display is:

WHERE Instr(Lcase(DSA.SubjectArea), 'new') > 0
Order By D.DatePublished desc

Documents may have multiple document types, but they will only have one subject area. This clause selects the subject area. I then only want to show unique documentids.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources