Professional Web Applications Themes

Need all employees in query - Coldfusion Database Access

I have a sql query to an access database. It returns employee information from the employees table and connects each employee to the programs they run from the programs table. The problem I am having is that if the employee does NOT head any programs their information is not being returned in the query. The ProgramContact is the Emp_ID from the employee table. So if the Emp_ID is not in the Programs table then the employee doesn't get listed in the output. My question, what do I have to change to get the query to return all employees regardless of ...

  1. #1

    Default Need all employees in query

    I have a sql query to an access database. It returns employee information
    from the employees table and connects each employee to the programs they run
    from the programs table. The problem I am having is that if the employee
    does NOT head any programs their information is not being returned in the
    query.

    The ProgramContact is the Emp_ID from the employee table. So if the Emp_ID
    is not in the Programs table then the employee doesn't get listed in the
    output.

    My question, what do I have to change to get the query to return all
    employees regardless of whether they head a program or not?

    help Please,

    Kim

    <cfquery name="rsGetEmployeeList" datasource="#request.dsn#">
    SELECT yda_Employees.Emp_ID, yda_Employees.Emp_Role,
    (yda_Employees.Emp_FirstName) + ' ' + (yda_Employees.Emp_LastName) AS
    EmployeeContact, yda_ProgramsServices.Program_ID,
    yda_ProgramsServices.ProgramTitle, yda_ProgramsServices.District
    FROM yda_Employees INNER JOIN yda_ProgramsServices ON yda_Employees.Emp_ID =
    yda_ProgramsServices.ProgramContact
    GROUP BY yda_Employees.Emp_ID, yda_Employees.Emp_Role,
    yda_Employees.Emp_FirstName, yda_Employees.Emp_LastName,
    yda_ProgramsServices.Program_ID, yda_ProgramsServices.ProgramTitle,
    yda_ProgramsServices.District
    ORDER BY yda_Employees.Emp_ID ASC, yda_ProgramsServices.District DESC
    </cfquery>


    KimMazz Guest

  2. #2

    Default Re: Need all employees in query

    Change INNER JOIN to LEFT JOIN, be aware that everything from the second table will be NULL if you're tempted to add WHERE clauses (and it's a slick way to zero in on those who are missing).
    JMGibson3 Guest

  3. #3

    Default Re: Need all employees in query

    Thank You!! I knew I had to change the join, but I didn't know what to
    change it to.

    Kim

    "JMGibson3" <webforumsusermacromedia.com> wrote in message
    news:dvmovg$hpt$1forums.macromedia.com...
    > Change INNER JOIN to LEFT JOIN, be aware that everything from the second
    > table will be NULL if you're tempted to add WHERE clauses (and it's a
    > slick way to zero in on those who are missing).

    KimMazz Guest

Similar Threads

  1. Writting to employees.xml
    By azul_oner in forum Macromedia Flex General Discussion
    Replies: 13
    Last Post: June 7th, 06:15 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. To all Freehand Employees(a stumper)
    By Heath Jackson in forum Macromedia Freehand
    Replies: 2
    Last Post: December 29th, 12:36 PM
  4. Replies: 14
    Last Post: July 25th, 03:06 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

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