sounds like you need an outer join to the tech_code table
SELECT nxstar_e.*
,nxsprodr.prod_desc
,(SELECT Count (id)
FROM nxsnotes
WHERE nxsnotes.id = nxstar_e.tar_num) AS Activities
,nxsteche.tech_name
FROM nxstar_e
,nxsprodr
,nxsteche
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
Note the *= on the last line - if the join condition is met then the
nxsteche.tech_code will be returned, otherwise NULL will be returned.
ps. Don't know what dB you're using: this is the syntax for sql server
Bert Dawson
> -----Original Message-----
> From: miles [mailto:[EMAIL PROTECTED]]
> Sent: 05 July 2000 22:48
> To: [EMAIL PROTECTED]
> Subject: RE: a very specifc query...
>
>
> >Can you just add the nxsteche table to the first query?
>
> nope. that creates more problems... because it further refines
> the query based on the fact that its looking for assigned_to to match
> the tech_code (the last AND)...and if it doesn't match then the query
> will produce nothing.
>
> I know I just tried it....however if you remove the last AND
> statement that you added it does sort of what Im looking for
> it to do...but not quite...it swaps out the tech_code for the
> user_name, just not the right ones!
>
> ><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>
>
> Wanna try this one more time, anyone else ?????
>
> Miles.
> --------------------------------------------------------------
> ----------------
> 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.
