Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Query Help - include
Author: Ian Skinner
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56330#304990
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.
Author: daniel kessler
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56330#304988
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
Author: Ian Skinner
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56330#304982
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.
Author: daniel kessler
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56330#304979
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>
|
May 24, 2012
|
Latest Fusion Authority Articles
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
