Help with Distinct SQL 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)

Help with Distinct SQL Statement

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2006-10-13 : 20:18:50

I'm trying to return a select statement with distinct values. I have 3 tables: Documents, DocumentAuthors and the linking table DocumentAuthorsRel. Since a document can have multiple authors, the DocumentAuthorsRel table holds the DocumentID and DocumentAuthorID values.

I simply want to run a query which displays a list of all the document titles (no document title should be repeated, i only want to show the title once, distinct) held in the documents table, and with the document title, show the last names of a documents author(s).

This is the statement Im using which is returning duplicated document titles (as a result of a document having multiple authors - found in the DocumentAuthorsRel table)

SELECT Documents.DocumentID, Documents.Title, DocumentAuthors.AuthorLName
FROM DocumentAuthors INNER JOIN
DocumentAuthorsREL ON DocumentAuthors.DocumentAuthorID = DocumentAuthorsREL.DocumentAuthorID INNER JOIN
Documents ON DocumentAuthorsREL.DocumentID = Documents.DocumentID

Any help would be appreciated.

thanks, -lance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 21:05:51

Your requirements conflict with each other.

You only want to show a document title once, but you want to show all authors. If you want to show all authors, you will have multiple rows, and the document title will be on each row.

You need to decide want you actually want, and it would be best if you could provide a sample output.

CODO ERGO SUM

ljp099
Yak Posting Veteran

79 Posts

Posted - 2006-10-13 : 22:01:48

Isn't there a way to create the statement so that the multiple authors can be displayed in a column? I know that I could code a .Net page to do this, and thought I could get a SQL query to do the same.

Title1 | author 1, author 2...
Title2 | author 1, author 3...

In ASP I could do this:

rs = Select Distinct Title from Documents

while not rs.eof
response.write "<tr><td>" & rs("title") & "</td><td>" &

rs1 = Select Lastname from DocumentAuthors InnerJoin ... Where Documents.DocumentID = rs("DocumentID")

while not rs1.eof
response.write("lastname") & "<br>"
wend

respone.write "</td></tr>"

rs.movenext
wend

Basically, your saying there is no way to have a SQL statement loop through table rows and, when hitting a designated column within a row, call another query statement to loop through another table of rows and display the results within the column, before moving onto the next row and doing the same. Correct?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-13 : 23:33:08

I didn't say anything like that. You just didn't explain what you wanted the first time.

Take a look at the link below.

Converting Multiple Rows into a CSV String (Set Based Method)
http://www.sqlteam.com/item.asp?ItemID=11021

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources