Query Help - include

I am using Oracle and I have a table for expenditures that stores a person's ID when the expenditures is approved.  The person's ID is from a People table.  When I query, I want to make a new variable that references the other table to put in their full name.  I have two problems doing this. 1 - I'm not so good at sql and the join only works if there's an id in the approved_by field.  This is going to be empty unless the ticket  has been approved, but I still want it to work whether it's approved or not.  Is that an outter join? 2.  Currently, I do p.fname AS approved_by_fname,p.fname AS approved_by_lname.   I'd like it to be one variable though, but I'm not sure how to format p.fname + p.lname AS approved_by_name. Here's my current query: <CFQUERY NAME="getSelect" DATASOURCE="dh" debug="yes" result="the_result">       SELECT e.expenditure_id,e.date_added,e.date_modified,e.approval,e.purpose,e.estimate,e.department,e.department_other,                 e.vendor_name,e.vendor_address,e.vendor_phone,e.vendor_contact,e.fei,e.state_employee,e.foundation,e.frs,                e.principal_investigator,e.requestor_fname,e.requestor_lname,e.requestor_email,e.approved_by,e.approval_date,p.fname AS approved_by_fname,p.fname AS approved_by_lname        FROM expenditures e, people p        WHERE e.approved_by = p.id                   AND date_added >= #url.start_date# AND date_added <= #url.end_date#            <cfswitch expression="#URL.approval#">             <cfcase value="pending">               AND approval = 'Pending'             </cfcase>             <cfcase value="approved">               AND approval = 'Approved'             </cfcase>             <cfcase value="denied">               AND approval = 'Denied'             </cfcase>             <cfcase value="all">               <!--- no WHERE needed - it's all of the tickets --->             </cfcase>           </cfswitch>        ORDER BY #the_sort# </CFQUERY>

daniel kessler wrote: > 1 - I'm not so good at sql and the join only works if there's an id in the approved_by field.  This is going to be empty unless the ticket  has been approved, but I still want it to work whether it's approved or not.  Is that an outter join? >   Yes it will be an LEFT or RIGHT outer join.  The direction determines which table you want ALL records from.  So if the expense table is on the left of the join use expense LEFT OUTER JOIN people on aField = bField.  If yo want it on the right then it would be people RIGHT OTER JOIN expense on aField = bField. > 2.  Currently, I do p.fname AS approved_by_fname,p.fname AS approved_by_lname.  I'd like it to be one variable though, but I'm not sure how to format p.fname + p.lname AS approved_by_name. >   In Oracle '||' is the concatenate operator so it would be p.fname || ' ' || p.lname AS approved_by_name.

I had continued to try and figure it out after I sent the email.  I did figure out a way to make it work. FROM expenditures e, people p WHERE e.approved_by = people.id (+) In Oracle, the + does the outer join.  And it worked well, but it wasn't explicit.  I don't know sql well enough to read that alter and go, "oh, I'm doing an outer join there".  I wanted explicit and that's what your code gave me.   Interestingly, it happens in the FROM rather than the WHERE cause of the ON. thank you. > Yes it will be an LEFT or RIGHT outer join.  The direction determines   > which table you want ALL records from.  So if the expense table is on > the left of the join use expense LEFT OUTER JOIN people on aField = > bField.  If yo want it on the right then it would be people RIGHT OTER > > JOIN expense on aField = bField. Thank you too for the explanation.  It was helpful. And the concatenation worked great too. daniel

daniel kessler wrote: > I had continued to try and figure it out after I sent the email.  I did figure out a way to make it work. > > FROM expenditures e, people p > WHERE e.approved_by = people.id (+) > > In Oracle, the + does the outer join.   Yes, that is the original outer join syntax for Oracle.  The ... OUTER JOIN ... ON ... syntax is the SQL 92 Standard syntax.  Oracle didn't adopt this standard until Version 9.x IIRC.