Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2002
    Posts
    189

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

    My thread titles need work, I know.

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Spudhead
    My thread titles need work, I know.
    Don't feel bad - I LOVVVVVED it

    Quote Originally Posted by Spudhead
    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/amachan...x?Pending=true

    HTH
    Last edited by pootle flump; 07-19-06 at 09:39.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Oh, God - every time I ask something on here I come away with more questions than answers

    <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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, assuming you are using SQL Server as the database engine and MS-Access to write and execute the query, then I'd suggest:
    Code:
    SELECT [tblDocuments].[documentName], [tblUsers].[userName ]
    ,  [tblDocumentApprovals].[approvalDate ]
       FROM [tblDocuments]
       WHERE EXISTS (SELECT *
          FROM [tblDocumentApprovals]
          WHERE [tblDocumentApprovals].[documentID] = [tblDocuments.id])
    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    Ok, assuming you are using SQL Server as the database engine and MS-Access to write and execute the query, then I'd suggest:
    Code:
    SELECT [tblDocuments].[documentName], [tblUsers].[userName ]
    ,  [tblDocumentApprovals].[approvalDate ]
       FROM [tblDocuments]
       WHERE EXISTS (SELECT *
          FROM [tblDocumentApprovals]
          WHERE [tblDocumentApprovals].[documentID] = [tblDocuments.id])
    -PatP
    Blimey Pat - from the man who once produced this:
    http://www.sqlteam.com/forums/topic....12&whichpage=3
    I am frankly disappointed. Any idea how many errors?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2002
    Posts
    189
    Umm.. yeah I can't get that one to work, either. Well, I did but it just gives me... gibberish

    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 () So I'm resigned to making my application code do the hard work, and keeping SQL queries doing what they do best.
    Last edited by Spudhead; 07-19-06 at 11:40.

  9. #9
    Join Date
    Jan 2002
    Posts
    189
    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.

    Code:
    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •