Professional Web Applications Themes

Select a list of items into an aliased field when doinga select - Coldfusion Database Access

OK I know this is going to sound weird, but I'm wondering if this is possible. I have a task table. (tblTask) These tasks can be assigned to multiple people from tblEmployee. There is a lookup table called tblEmployeeTask which contains nothing but Task ID's and Employee ID's so that the same task can be assigned to multiple people. Now I am doing a select statment to display the tasks and feeding that query into a display module. The display module expects one query and will display the columns from the query returned. It will ONLY accept one query so ...

  1. #1

    Default Select a list of items into an aliased field when doinga select

    OK I know this is going to sound weird, but I'm wondering if this is possible.
    I have a task table. (tblTask) These tasks can be assigned to multiple people
    from tblEmployee. There is a lookup table called tblEmployeeTask which contains
    nothing but Task ID's and Employee ID's so that the same task can be assigned
    to multiple people. Now I am doing a select statment to display the tasks and
    feeding that query into a display module. The display module expects one query
    and will display the columns from the query returned. It will ONLY accept one
    query so I need all the fields to be returned in the one query. Now since the
    task can be assigned to multiple people, I need a way in my original query to
    get a comma delimeted list (or a list seperated by carriage returns for
    display) containing the first and last names of the people the task is assigned
    to. So for example, the query would return: vchrTaskSubject: 'Foo'
    dtmStartDate: '03/18/2005' dtmEndDate: '03/30/2005' AssignedTo: 'Fred
    Flinstone, Barney Rubble, Pete Wackadoo' So I just need a way int eh query to
    select that list into a return variable. I hope this made sense. THANKS

    ehaemmerle Guest

  2. #2

    Default Re: Select a list of items into an aliased field whendoing a select

    Just ORDER BY task in your query, then in your CFOUTPUT you would use the GROUP
    = attribute, something like this:

    <cfoutput query="query_name" group="task>
    #task#<br>
    <cfoutput>
    #Employee#<br>
    </cfoutput>
    </cfoutput>

    Phil


    paross1 Guest

  3. #3

    Default Re: Select a list of items into an aliased field whendoing a select

    Thanks for the reply. Sorry I wasnt clear. I am actually feeding the query into
    a display module. I cannot edit the display module directly as it is a module
    that we are using that will take a query and automatically display the columns
    and values from the given query so all our query results have a consistent look
    and feel. So thats why I have to have all my data IN the original query. I
    cannot mess with the actual oputput on the display side as the module handles
    that.

    ehaemmerle Guest

  4. #4

    Default Re: Select a list of items into an aliased field whendoing a select

    Sorry, I was editing my original post by adding a query when you replied. The
    best that you can do is get you list of tasks and employees, but the task name
    will show up in each row with the employee name. You have to use the gymnastics
    in the output to list the task only once for every employee name.

    <cfquery name="query_name" datasource="dsn">
    SELECT t.task, e.Employee
    FROM tblTask t, tblEmployee e, tblEmployeeTask et
    WHERE t.taskID = et.taskID
    AND e.employeeID = et.employeeID
    ORDER BY t.task
    </cfquery>


    paross1 Guest

Similar Threads

  1. Replies: 2
    Last Post: October 14th, 10:07 PM
  2. Replies: 0
    Last Post: September 24th, 05:39 PM
  3. SELECT to find items NOT common in a table
    By Ivan Demkovitch in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 08:10 PM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139