JOINing... but not with the multiple rows thing.

My thread titles need work, I know. :o

Ok, lets say I've got:

tblDocuments
id INT PK
documentName VARCHAR

tblUsers
id INT PK
userName VARCHAR

tblDocumentApprovals
userID INT
documentID INT
approvalDate DATETIME

If I want to get a list of documents, and the users who've signed them off (if any), I'd do something like:

SELECT [tblDocuments].[documentName], [tblUsers].[userName ], [tblDocumentApprovals].[approvalDate ]
FROM [tblDocuments]
LEFT JOIN [tblDocumentApprovals] ON [tblDocumentApprovals].[documentID] = [tblDocuments.id]
INNER JOIN [tblUsers] ON [tblUsers].[id] = [tblDocumentApprovals].[userID]

...which is lovely. Except - I don't want a row returned for each user that's signed it off. I want one row for each document, with a field containing a list of the people who've signed it off.

I know that it's bad design. I was reading an article only yesterday on how they're putting this kind of thing into the latest version of Access, and how it's a bit of a kludge. But it'd really, really help me.

How do you do it?My thread titles need work, I know. :oDon't feel bad - I LOVVVVVED it :D

I was reading an article only yesterday on how they're putting this kind of thing into the latest version of Access, and how it's a bit of a kludge. But it'd really, really help me.Really? Currently you have to bugger about with recordsets in VBA functions.

Anyway - I mostly got involved because of your post title but I suppose I should help really. How about this:
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

HTH|||Oh, God - every time I ask something on here I come away with more questions than answers :S :o

<scuttles off to find out what a FUNCTION is and how it differs from a stored proc>

I think that's what I'm after, though. Thanks.|||A (scalar) function is basically a bit of code that takes input, processes it (according to some technical\ busniess requirement) and returns a single value result. Not to be confused with In Line Table and Multi Line Table returning functions.

The idea here is you use the function in the SELECT clause of SQL, passing parameters (often other columns) and display the return.

Check out CREATE FUNCTION in BoL. If you've used other programming languages then it will seem jolly familiar

HTH|||Some principle differences to a sproc (btw) are that it ALWAYS returns a result, must be deterministic (the output can only vary if the input varies so no use of GetDate() etc) and can be used in a query.

HTH|||Ok, assuming you are using SQL Server as the database engine and MS-Access to write and execute the query, then I'd suggest:SELECT [tblDocuments].[documentName], [tblUsers].[userName ]
, [tblDocumentApprovals].[approvalDate ]
FROM [tblDocuments]
WHERE EXISTS (SELECT *
FROM [tblDocumentApprovals]
WHERE [tblDocumentApprovals].[documentID] = [tblDocuments.id])-PatP|||Ok, assuming you are using SQL Server as the database engine and MS-Access to write and execute the query, then I'd suggest:SELECT [tblDocuments].[documentName], [tblUsers].[userName ]
, [tblDocumentApprovals].[approvalDate ]
FROM [tblDocuments]
WHERE EXISTS (SELECT *
FROM [tblDocumentApprovals]
WHERE [tblDocumentApprovals].[documentID] = [tblDocuments.id])-PatPBlimey Pat - from the man who once produced this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63512&whichpage=3
I am frankly disappointed. Any idea how many errors? :(|||Umm.. yeah :S I can't get that one to work, either. Well, I did but it just gives me... gibberish :o

I have to admit... I've gone back to standard table joins with lots of 'duplicate' entries in rows. (Is there a technical name for this sort of thing?) I'm very grateful for your help, pootle_flump - but something about the idea of writing this little function to go and get me the data I needed... it just seemed... a bit inelegant (:D) So I'm resigned to making my application code do the hard work, and keeping SQL queries doing what they do best.|||I also have to admit that my original post wasn't entirely accurate: I wanted to concentrate on the theory of the thing, not the fact that the "documents" in question are, in fact, HTML emails, and that users are involved in both signing off emails and in being on the account management team for them. Sorry if that didn't help.

If it is any use, here is my current stored proc in all its glory. As you can see, I started with this whole headache because of the doubling-up of users as both signer-offers and as account team members: to get the detail of one email that's been signed off by two members of a four-person account team, I'm going to be generating eight rows of largely duplicate data. This seems silly.

SELECT
[tblFiles].[id] AS fileID,
[tblFiles].[client_id],
[tblFiles].[campaign_id],
[tblFiles].[filepath],
[tblFiles].[file_info],
[tblFiles].[date_added],
[tblClients].[clientname],
[tblClients].[email_root_url],
[tblCampaigns].[campaigncode],
[tblCampaigns].[description],
[tblUsers].[id] AS signoffUserID,
[tblUsers].[username] AS signoffUserName,
[tblEmailSignoffs].[signoff_date],
[ClientAccountTeam].[accountTeamMemberId],
[ClientAccountTeam].[accountTeamMemberName]
FROM [tblFiles]
LEFT JOIN [tblEmailSignoffs] ON [tblEmailSignoffs].[file_id] = [tblFiles].[id]
LEFT JOIN [tblUsers] ON [tblEmailSignoffs].[user_id] = [tblUsers].[id]
INNER JOIN [tblClients] ON [tblFiles].[client_id] = [tblClients].[id]
INNER JOIN [tblCampaigns] ON [tblFiles].[campaign_id] = [tblCampaigns].[id]
LEFT JOIN(
SELECT
[tblClients].[id] AS accountID,
[tblAccountTeams].[user_id] AS accountTeamMemberId,
[tblUsers].[username] AS accountTeamMemberName
FROM [tblClients]
INNER JOIN [tblAccountTeams] ON [tblAccountTeams].[client_id] = [tblClients].[id]
INNER JOIN [tblUsers] ON [tblUsers].[id] = [tblAccountTeams].[user_id]
) AS [ClientAccountTeam]
ON [ClientAccountTeam].[accountID] = [tblFiles].[client_id]

WHERE [tblFiles].[content_type]='text/html'
ORDER BY [tblFiles].[id]sql