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

Other Forums

MS Access

Need help with SQL Select statement

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2003-11-12 : 12:54:13

Im trying to use this statement:

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

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-13 : 05:25:13

of course this doesn't work...."The problem is that it doesnt return only distinct values for DocumentID."...because you're not asking for

SELECT Distinct D.DocumentID 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

DISTINCT applies to the full set of COLUMNS selected...and NOT just the COLUMN IMMEDIATELY AFTER THE DISTINCT STATEMENT....(you MAY also need to trim off the order by bit)

GreySky
Starting Member

20 Posts

Posted - 2003-11-13 : 09:27:20

Please see my response to your question on the MS Access Message Boards hosted by Microsoft.

-------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources