Need Help with SQL Select/Join 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/Join Statement

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2003-11-19 : 23:48:27

Im having a SQL nightmare.

Simply put, I have a table, "Documents" which has a uniqueID for each record "DocumentID", and another table, "DocumentTYpes", with a uniqueID, "DocumentTypeID", and the tables are linked via another table, "DocumentTypesREL", which has two columns, "DocumentID" and "DocumentTypeID" (foreign keys for the respective tables). Documents can therefore have multipe document types:

DocumentTypesRel (Data)

DocumentID DocumentTypeID
1 2
2 4
1 3

If you wanted to return a recordset so that you only show a record once (Distinct DocumentID), how would you do it? When I join these tables, because of the duplicate values for DocumentID 1, in this example, this record is displayed twice in the recordset? So, my question is how do you display records only once when you are joining tables using a linked table such as this where foreign keys can appear multiple times?

Thanks for any help,
lance

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-20 : 00:05:43

Does this help?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30648

Owais

Where there's a will, I want to be in it.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-20 : 00:20:09

If you don't join onto the types it will only appear once.
How would you like the data displayed ?

Damian

ljp099
Yak Posting Veteran

79 Posts

Posted - 2003-11-20 : 01:57:14

Here's the statement Im trying to use:

SELECT Distinct D.*, DocumentTypes.AccessLevel from Documents D Inner Join DocumentTypesREL ON DocumentTypes.DocumentTYpeID=DocumentTypesREL.DocumentTYpeID Inner Join D.DocumentID=DocumentTypesREL.DocumentID Order By D.Title desc

Im trying to display the data like this:

Document ID, Document Title, Access Level, Date Published (all these fields come from the Documents table except for Access Level which comes from the DocumentTypes which is why it's in the select group), but I get these duplicate values. Whats the solution?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-20 : 02:48:36

If a document has say, three document types, which means there will be three access levels associated with it. So question is, which access level do you want to show? Best case you could show them as a comma-separated list for each document, but it wouldnt make much sense to the user since you still wouldn't be able to see which access level is associated with which document type. Do I make sense here?

Owais

Where there's a will, I want to be in it.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-11-20 : 08:53:02

Aggregates are your friend...

but you have to determine which is important to return in the result set

use MIN() or MAX() and GROUP BY the rest.

ljp099
Yak Posting Veteran

79 Posts

Posted - 2003-11-20 : 13:40:46

Thanks for the replies, however, I still dont understand how to write the SQL statement. I can handle the dispaly of the items in my code, what I need help with is getting the SQL statement to work to return the desired recordset.

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-20 : 13:54:38

What is your query returning? And what should it return? Please show us both.

Tara

TimChenAllen
Starting Member

45 Posts

Posted - 2003-11-21 : 06:47:00

quote:
Originally posted by ljp099

Thanks for the replies, however, I still dont understand how to write the SQL statement. I can handle the dispaly of the items in my code, what I need help with is getting the SQL statement to work to return the desired recordset.

If I understand what you are trying to do (and I'm not sure), here is what I came up with:

SELECT     Documents.DocumentID, Documents.Title, MAX(DocumentTypes.AccessLevel)
FROM       DocumentTypesREL
INNER JOIN DocumentTypes ON DocumentTypesREL.DocumentTypeID = DocumentTypes.DocumentTypeID
INNER JOIN Documents ON DocumentTypesREL.DocumentID = Documents.DocumentID
GROUP BY   Documents.DocumentID, Documents.Title
ORDER BY   Documents.Title DESC

This assumes that you want to show the MAXimum access level for each document.

--
Timothy Chen Allen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources