--============_-1249300486==_ma============
Content-Type: text/plain; charset="us-ascii" ; format="flowed"
1.) <CFQUERY Name="CaseAndNotes" DataSource="CCMast">
SELECT nxstar_e.*, nxsprodr.prod_desc,
(SELECT Count (id)
FROM nxsnotes
WHERE nxsnotes.id = nxstar_e.tar_num) AS Activities
FROM nxstar_e, nxsprodr
WHERE nxsprodr.prod_code = nxstar_e.prod_id
AND cust_code = #Cookie.User_ID#
ORDER BY nxstar_e.tar_num desc
</cfquery>
2.) <CFQUERY Name="Notes" DataSource="CCMast">
SELECT nxsteche.tech_name
FROM nxsteche, nxstar_e, nxsprodr
WHERE nxstar_e.entered_by = nxsteche.tech_code
AND cust_code = #Cookie.User_ID#
</cfquery>
The first query above produces the following stream of data:
Case# | Notes | Status | Contact | Product | Problem |
Supported | DateEntered
<http://192.168.1.160/prod_mirror/ccare/internet/case-detail.cfm?id=4097>
4097 | 3 | CLOSED | Barbara Boscia | SiteMind-NT | CONFIG
| Joyce Hampson | 29-Mar-00
3623 | 1 | CLOSED | Savez Rashibi | SiteMind-2500 | CRASH
| Joyce Hampson | 17-Feb-00
2940 | 8 | CLOSED | Joshua Shaw | SiteMind-NT | QUESTION |
Joyce Hampson | 01-Dec-99
Note that teh field/column supported always yields the same entry
Joyce Hampson...
That's because the code that's generating joyce's name is actually do
a query against
the 2nd query...which is where the problem lies. The column in question is
nxstar_e.entered_by which is a code that in joyces' case looks like JHAMP
....which gets turned into Joyce Hampson via the 2nd query. Tha's
not quite what Im
wanting it to do...While Joyce is actually supposed to be in the
returned record set...
she's only one of three people that should be returned for this
search. The other
two are JFREED and ABRAY which would yield respectively John Freednor
and Aron Brayson...
Case# | Notes | Status | Contact | Product | Problem |
Supported | DateEntered
<http://192.168.1.160/prod_mirror/ccare/internet/case-detail.cfm?id=4097>
4097 | 3 | CLOSED | Barbara Boscia | SiteMind-NT | CONFIG
| Joyce Hampson | 29-Mar-00
3623 | 1 | CLOSED | Savez Rashibi | SiteMind-2500 | CRASH
| John Freednor | 17-Feb-00
2940 | 8 | CLOSED | Joshua Shaw | SiteMind-NT | QUESTION |
Aron Brayson | 01-Dec-99
So here's my question...should query one and two be combined...if so..how ?
I apologize for the length of the post...
Miles.
--============_-1249300486==_ma============
Content-Type: text/html; charset="us-ascii"
<!doctype html public "-//W3C//DTD W3 HTML//EN">
<html><head><style type="text/css"><!--
blockquote, dl, ul, ol, li { margin-top: 0 ; margin-bottom: 0 }
--></style><title>a very specifc query...</title></head><body>
<div>1.) <CFQUERY Name="CaseAndNotes"
DataSource="CCMast"><br>
SELECT nxstar_e.*, nxsprodr.prod_desc,<br>
(SELECT Count (id)<br>
FROM nxsnotes <br>
WHERE nxsnotes.id =
nxstar_e.tar_num) AS Activities<br>
FROM nxstar_e, nxsprodr<br>
WHERE nxsprodr.prod_code = nxstar_e.prod_id<br>
<span
></span> AND cust_code = #Cookie.User_ID#<br>
ORDER BY nxstar_e.tar_num desc<br>
</cfquery></div>
<div> </div>
<div>2.) <CFQUERY Name="Notes"
DataSource="CCMast"><br>
SELECT nxsteche.tech_name<br>
FROM nxsteche, nxstar_e, nxsprodr<br>
WHERE nxstar_e.entered_by = nxsteche.tech_code<br>
AND cust_code = #Cookie.User_ID#<br>
</cfquery></div>
<div> </div>
<div>The first query above produces the following stream of
data:</div>
<div> </div>
<div>Case# | Notes | Status |
Contact | Product | Problem |
Supported | DateEntered <a
href="http://192.168.1.160/prod_mirror/ccare/internet/case-detail.cfm?id=4097"
></a></div>
<div>4097 | 3 | CLOSED | Barbara
Boscia | SiteMind-NT | CONFIG |
Joyce Hampson | 29-Mar-00</div>
<div>3623 | 1 | CLOSED | Savez
Rashibi | SiteMind-2500 | CRASH |
Joyce Hampson | 17-Feb-00</div>
<div>2940 | 8 | CLOSED | Joshua
Shaw | SiteMind-NT | QUESTION |
Joyce Hampson | 01-Dec-99</div>
<div><br></div>
<div>Note that teh field/column supported always yields the same
entry Joyce Hampson...</div>
<div><br></div>
<div>That's because the code that's generating joyce's name is
actually do a query against</div>
<div>the 2nd query...which is where the problem lies. The
column in question is</div>
<div>nxstar_e.entered_by which is a code that in joyces' case looks
like JHAMP</div>
<div>....which gets turned into Joyce Hampson via the 2nd
query. Tha's not quite what Im</div>
<div>wanting it to do...While Joyce is actually supposed to be in the
returned record set...</div>
<div>she's only one of three people that should be returned for this
search. The other</div>
<div>two are JFREED and ABRAY which would yield respectively John
Freednor</div>
<div>and Aron Brayson...</div>
<div><br></div>
<div>Case# | Notes | Status |
Contact | Product | Problem |
Supported | DateEntered <a
href="http://192.168.1.160/prod_mirror/ccare/internet/case-detail.cfm?id=4097"
></a></div>
<div>4097 | 3 | CLOSED | Barbara
Boscia | SiteMind-NT | CONFIG |
Joyce Hampson | 29-Mar-00</div>
<div>3623 | 1 | CLOSED | Savez
Rashibi | SiteMind-2500 | CRASH |
John Freednor | 17-Feb-00</div>
<div>2940 | 8 | CLOSED | Joshua
Shaw | SiteMind-NT | QUESTION |
Aron Brayson | 01-Dec-99</div>
<div><br></div>
<div><br></div>
<div>So here's my question...should query one and two be
combined...if so..how ?</div>
<div><br></div>
<div>I apologize for the length of the post...</div>
<div><br></div>
<div>Miles.</div>
</body>
</html>
--============_-1249300486==_ma============--
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
