I'm stumped on this. How to I retrieve records that do not
match (I'm wanting to display orphaned Companies that are not
associated with Projects). Tried and using NOT and NOT
IN() without success.
SELECT C.company, C.companyID, C.companyCity, C.companyState,
C.companyZip,
Mark Leder wrote:
I'm stumped on this. How to I retrieve records that do not match (I'm
wanting to display orphaned Companies that are not associated with
Projects). Tried and using NOT and NOT IN() without success.
SELECT C.company, C.companyID, C.companyCity, C.companyState,
Try this:
SELECT company, companyID, companyCity, companyState, companyZip,
FROM dbTable_Clients_Companies_List
WHERE companyID Not in (Select companyID From dbtable_Clients_Projects_List)
ORDER BY company ASC
this sould do...
Greetings / Grüsse
Gert Franz
Customer Care
Railo Technologies
You could use the GROUP BY and count() function. See
http://www.petefreitag.com/item/169.cfm
HTH,
Aaron
On 11/16/06, Mark Leder [EMAIL PROTECTED] wrote:
I'm stumped on this. How to I retrieve records that do not match (I'm
wanting to display orphaned Companies that are not associated with
Jim,
Thank you, that worked perfectly. Admittedly, I get confused by the LEFT/ON
attribs in queries.
Thanks,
Mark
-Original Message-
From: Jim Wright [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 16, 2006 11:56 AM
To: CF-Talk
Subject: Re: SQL Problem
Mark Leder wrote:
I'm
That works too. Thanks Dave.
Thanks,
Mark
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 16, 2006 11:56 AM
To: CF-Talk
Subject: RE: SQL Problem
I'm stumped on this. How to I retrieve records that do not match (I'm
wanting to display orphaned
SELECT TOP 1 (controlID, memberID, memberSubmitDate)
FROM tblList
order by membersubmitdate desc
On 3/9/06, Mark Leder [EMAIL PROTECTED] wrote:
How do I do a single record select only choosing the most recent record
based on a SQL timestamp (date/time)? For example, I
maybe...
SELECT TOP 1 (controlID, memberID, memberSubmitDate)
FROM tblList
ORDER BY memberSubmitDate DESC
-Original Message-
From: Mark Leder [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 09, 2006 1:36 PM
To: CF-Talk
Subject: SQL Problem
How do I do a single
Could just add an order by membersubmitdate desc and strip out the where
clause.
On 3/9/06, Mark Leder [EMAIL PROTECTED] wrote:
How do I do a single record select only choosing the most recent record
based on a SQL timestamp (date/time)? For example, I could have two
records, one posted
Mark,
I'd try this:
SELECT TOP 1 (controlID, memberID, memberSubmitDate)
FROM tblList
ORDER BY memberSubmitDate DESC
HTH,
Matthieu
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234884
Archives:
Try this:
SELECT TOP 1 (controlID, memberID, memberSubmitDate)
FROM tblList
ORDER BY memberSubmitDate DESC
--Jeff
On 3/9/2006 1:36 PM, Mark Leder wrote:
How do I do a single record select only choosing the most recent record
based on a SQL timestamp (date/time)? For example, I could have two
From: Mark Leder [mailto:[EMAIL PROTECTED]
SELECT TOP 1 (controlID, memberID, memberSubmitDate)
FROM tblList
WHERE memberSubmitDate ???
SELECT
TOP 1
controlID,
memberID,
memberSubmitDate
FROM
tblList
ORDER BY
memberSubmitDate DESC
Thanks everyone.
It worked w/o the ( ) in the select statement.
Thanks,
Mark
-Original Message-
From: Michael T. Tangorre [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 09, 2006 1:45 PM
To: CF-Talk
Subject: RE: SQL Problem
From: Mark Leder [mailto:[EMAIL PROTECTED]
SELECT TOP 1
Session is most likely I reserved word. Try [session].division and better
yet renaming the table if you can.
-Original Message-
From: Ian Vaughan [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 22, 2005 9:08 AM
To: CF-Talk
Subject: SQL Problem
Hi
Can anybody help on why the following
looks kinda like you may have some cf logic in your sql code...
CFQUERY DATASOURCE=#Application.DSN# NAME=update
SELECT docid, doctitle, docdate
FROM doc
WHERE username ='#Session.Fname# #Session.Lname#'
AND
directorate='team'
AND
session.division='ss'
ORDER BY docid desc, docdate desc
/CFQUERY
The problem is you have session.division='ss'do you mean
division='ss'?? What is the database field name, not the session variable name?
Hi
Can anybody help on why the following query is returning the error
missing expression shown below ?
If I remove the AND
session.division should not be a field name in your databaseif it is
you should change it ;-)
Oh ya...and it's standard practice to store user names broken out into
first/last/initial fields (you can add suffix and prefix as well for Dr. and
Charles III).
Cheers
Bryan Stevenson B.Comm.
I am curious, is session.division an actual column name within the doc
table? Just seems a little odd is why I ask.
On Tue, 22 Feb 2005 14:08:22 -, Ian Vaughan
[EMAIL PROTECTED] wrote:
Hi
Can anybody help on why the following query is returning the error
missing expression
From: Eric Creese
How come when I issue this statement
select * from persondemographic(nolock)
where demographicgroupid =3
and demographicitemid = 4
and cast(demographicvaluedesc as datetime) getdate()-1
I get a result set, but all the fields and when I issue this statement
select
demographicgroupid =3
and demographicitemid = 4
CASE
WHEN isdate(demographicvaluedesc) =1 THENcast(demographicvaluedesc as datetime) getdate()
ELSE
END
-Original Message-
From: Philip Arnold [mailto:[EMAIL PROTECTED]
Sent: Friday, March 26, 2004 12:27 PM
To: CF-Talk
Subject: RE: SQL
[mailto:[EMAIL PROTECTED]
Sent: Friday, March 26, 2004 12:27 PM
To: CF-Talk
Subject: RE: SQL problem/Question
From: Eric Creese
How come when I issue this statement
select * from persondemographic(nolock)
where demographicgroupid =3
and demographicitemid = 4
and cast(demographicvaluedesc
You can use grouped output, but unless your title is unique you will
have to order classes by ID
Select c.classId, c.classTexts, c.classDescription,
cc.classCodeSection, cc.classDate, cc.classTime,
cc.classLocation, cc.classInstructor
FROM CLASSES c, CLASSCODES cc
WHERE c.classId = "">
AND ...
Brother thank you! That was it!
I'm going to have to read up on this.
Gil
-Original Message-
From: Pascal Peters [mailto:[EMAIL PROTECTED]
Sent: Monday, February 16, 2004 9:27 AM
To: CF-Talk
Subject: RE: sql, problem with
You can use grouped output, but unless your title is unique you
No need to post your question twice.. be patient, someone will respond if
they know the answer.:-)
-Original Message-
From: Mickael [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 12:42 PM
To: CF-Talk
Subject: SQL Problem
Hello All,
I am having problems with an update
, December 16, 2003 12:48 PM
Subject: RE: SQL Problem
No need to post your question twice.. be patient, someone will respond if
they know the answer.:-)
-Original Message-
From: Mickael [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 12:42 PM
To: CF-Talk
Subject: SQL Problem
Hello
: Tuesday, December 16, 2003 12:48 PM
Subject: RE: SQL Problem
No need to post your question twice.. be patient, someone will respond if
they know the answer.:-)
-Original Message-
From: Mickael [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 12:42 PM
To: CF-Talk
Subject: SQL Problem
, December 16, 2003 12:56 PM
To: CF-Talk
Subject: Re: SQL Problem
Hi Michael,
Sorry for the double post but I thought there was something up with the
list, I posted the first one two hours ago didn't see it on the list and
sure enough as soon as I post again the both show up
- Original Message
To: CF-Talk
Sent: Tuesday, December 16, 2003 12:55 PM
Subject: RE: SQL Problem
Try Trimming your vars especially since they were text before you
converted them. If it was char type text there might be some padding that
you don't see..
-Original Message-
From: Mickael [mailto:[EMAIL
But it is not the date field that is causing the issue, it the what I am using as the issue number
- Original Message -
From: Dwayne Cole
To: CF-Talk
Sent: Tuesday, December 16, 2003 1:18 PM
Subject: RE: SQL Problem
Try putting # signs around the date field.I had this problem before
Please respond
to cf-talk
But it is not the date field that is causing the issue, it the what I am
using as the issue number
- Original Message -
From: Dwayne Cole
To: CF-Talk
Sent: Tuesday, December 16, 2003 1:18 PM
Subject: RE: SQL Problem
Try putting # signs around the date field.I had
Update IssueRegister
SET IssueStatus = '#form.IssueStatus#',
Response = '#form.Response#',
ResponseUserId = '#session.activeuser.USER_NAME#',
DateofResponse = #CreateODBCdate(now())#
Where IssueNumber = #form.issuenumber#
The error that I get from CF is
The search key was not found in any
Stephen
Thanks a lot that did it.I can now stop banging my head against the wall
- Original Message -
From: Stephen Hait
To: CF-Talk
Sent: Tuesday, December 16, 2003 2:39 PM
Subject: Re: SQL Problem
Update IssueRegister
SET IssueStatus = '#form.IssueStatus#',
Response
Thank you Mosh. Your code works perfectly.
I thought there might be a way to convert column data
from a string into a comma-separated list using
whatever token you needed. So if a column row
contained 1,2,3 (1 element) it would be converted to
1,2,3 (3 seperate elements). It was just a
thought.
Brian:
You probably want o be using wildcard matching instead of the IN operator.
Your query should be changed to something like:
WHERE (
Emonth LIKE '%,#frmEMonth#,%'
OR Emonth LIKE '#frmEMonth#,%'
OR Emonth LIKE
cfquery name=myQuery
SELECT *
FROM TABLE
ORDER BY year DESC
/cfquery
cfoutput query=myQuery
#CountryName#, #Value#, #Year#br
/cfoutput
regards
badal Tyagi
HCL Perot Systems
Noida, India
-Original Message-
From: Hugo Ahlenius [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 17, 2003
The optional feature not implemented message iirc occurs when sql server
receives a string instead of a datetime value ... also, afaik, a timestamp
is supposed to be a uniquely identifying variable, so you shouldn't use them
unless the plan is to use it as the or a unique id for the table... I
How about ValueList(QueryName.FieldName)
-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 15, 2002 7:28 AM
To: CF-Talk
Subject: SQL Problem
For you SQL guys out there. Do you know if there is an easy way to combine
values from multiple rows?
select name+', '+id as Name_Concat from MyTable
is this what u where looking for?
..tony
Tony Weeg
Senior Web Developer
Information System Design
Navtrak, Inc.
Fleet Management Solutions
www.navtrak.net
410.548.2337
-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Message-
From: Tony Weeg [mailto:[EMAIL PROTECTED]]
Sent: 15 August 2002 13:25
To: CF-Talk
Subject: RE: SQL Problem
select name+', '+id as Name_Concat from MyTable
is this what u where looking for?
.tony
Tony Weeg
Senior Web Developer
Information System Design
Navtrak, Inc.
Fleet
my advice. whenever you have SQL server errors that are really weird, go to
http://groups.google.com and do a search in there. It will hit a search in
alot of SQL Server newsgroups
Anthony Petruzzi
Webmaster
954-321-4703
http://www.sheriff.org
-Original Message-
From: Ian Lurie
Try changing the protocol used in the server and in
the ODBC entry or review the TCP/IP settings.
I think the last is the best
regards
Mario
--- [EMAIL PROTECTED] escribió: my advice.
whenever you have SQL server errors that
are really weird, go to
http://groups.google.com and do a search in
You'd have to break it up for each value of table_field:
where
cfset first=0
CFLOOP INDEX=index_nameLIST=table_field
DELIMITERS=item_delimiter
cfif first gt 0 or /cfif
#index_name# in ('url.variable')
cfset first=1
/CFLOOP
-Original Message-
From: Harold
I am building a dynamic query, if a certain form field is selected it adds
the following statement to the end of the where clause: and table_field in
('url.variable')
The table field houses a comma delimited list of numbers and the url
variable is a comma delimited list as well.
The query
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
As usual, I managed to answer the question myself by looking around
properly. This thread
(http://forums.allaire.com/devconf/Thread.cfm?Message_ID=556006_#Me
ssage556006) at the forums tells all.
Sorry for bandwidth-wasting.
Steve
-BEGIN PGP
Steve
SELECT type_id, type_id ' (' description ')' AS temp
FROM CallTypes
WHERE new
ORDER BY sequence
Is this a literal excerpt?
"WHERE new" doesn't make sense to me. It should be WHERE something =
something else.
"ORDER BY sequence" doesn't either - "sequence"
can't have "like ="
use like for strings and = for numeric comparisons (i.e. like "rabbit" or
= 2333
can't have =Size in your select statement. I'm not sure what you're trying
to do with that part.
-Original Message-
From: MichaelFox [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 23,
vPath, =size ?
what are you trying to do here???
From Scope() ?? what is that???
Please specify a straight forward query!!!
SELECT FileName, DocTitle, DocSubject, DocCreatedTM, DocAuthor, vPath, =
Size, Characterization FROM SCOPE() WHERE Characterization LIKE =
('""strText""') AND
47 matches
Mail list logo
