Can you just add the nxsteche table to the first query?
<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
,nxsteche.tech_name /** new line **/
FROM nxstar_e
,nxsprodr
,nxsteche /** new line **/
WHERE nxsprodr.prod_code = nxstar_e.prod_id
AND cust_code = #Cookie.User_ID#
AND nxstar_e.entered_by = nxsteche.tech_code/** new line **/
ORDER BY nxstar_e.tar_num desc
</cfquery>
Bert Dawson
> -----Original Message-----
> From: miles [mailto:[EMAIL PROTECTED]]
> Sent: 05 July 2000 19:33
> To: [EMAIL PROTECTED]
> Subject: a very specifc query...
>
>
> --============_-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.c
> fm?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.c
> fm?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-det
> ail.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-det
> ail.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=list
s/cf_talk or send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body.
------------------------------------------------------------------------------
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.
