t;
> Jenny
>
> >>-Original Message-
> >>From: Michael Grant [mailto:mgr...@modus.bz]
> >>Sent: 21 June 2011 23:27
> >>To: cf-talk
> >>Subject: Re: SQL Query Problem
> >>
> >>
> >>
> >>Right, but if tha
Gavin-Wear <
>>jenn...@fasttrackonline.co.uk> wrote:
>>
>>>
>>> I was waiting for a comment on that.
>>>
>>> It's a very small table :)
>>>
>>> >>-Original Message-
>>> >>From: Michael Gra
Tue, Jun 21, 2011 at 5:15 PM, Jenny Gavin-Wear <
jenn...@fasttrackonline.co.uk> wrote:
>
> I was waiting for a comment on that.
>
> It's a very small table :)
>
> >>-Original Message-
> >>From: Michael Grant [mailto:mgr...@modus.bz]
I was waiting for a comment on that.
It's a very small table :)
>>-Original Message-
>>From: Michael Grant [mailto:mgr...@modus.bz]
>>Sent: 21 June 2011 19:46
>>To: cf-talk
>>Subject: Re: SQL Query Problem
>>
>>
>
She didn't provide column names...
On Tue, Jun 21, 2011 at 1:45 PM, Michael Grant wrote:
>
> Off topic, but the "Select *" made me shudder.
>
>
> On Tue, Jun 21, 2011 at 2:25 PM, Jenny Gavin-Wear <
> jenn...@fasttrackonline.co.uk> wrote:
>
> >
> > Looks like I went with the vote, lol
> >
> > Ma
+420
On Tue, Jun 21, 2011 at 2:45 PM, Michael Grant wrote:
>
> Off topic, but the "Select *" made me shudder.
>
>
> On Tue, Jun 21, 2011 at 2:25 PM, Jenny Gavin-Wear <
> jenn...@fasttrackonline.co.uk> wrote:
>
>>
>> Looks like I went with the vote, lol
>>
>> Many thanks for all replies, and fast
Off topic, but the "Select *" made me shudder.
On Tue, Jun 21, 2011 at 2:25 PM, Jenny Gavin-Wear <
jenn...@fasttrackonline.co.uk> wrote:
>
> Looks like I went with the vote, lol
>
> Many thanks for all replies, and fast too :)
>
> Some payments from Paypal transactions, some manually entered on
Looks like I went with the vote, lol
Many thanks for all replies, and fast too :)
Some payments from Paypal transactions, some manually entered on profiles.
Legacy code :/
Jenny
select * from tbl_members
where
(datepart(m,paid) = #session.month# and datepart(,paid) = #session.year#
AND
mem
That looks familiar! :-)
On Tue, Jun 21, 2011 at 1:09 PM, Stephane Vantroyen wrote:
>
> I would do it this way :
>
> select b.*
> from b
> where b.id not in (select a.id from a)
>
>
>
> >How about:
> >
> >select b.*
> >from b
> >left outer join a on b.id = a.id
> >where a.id is null
> >
> >Car
I would do it this way :
select b.*
from b
where b.id not in (select a.id from a)
>How about:
>
>select b.*
>from b
>left outer join a on b.id = a.id
>where a.id is null
>
>Carl
>
>On 6/21/2011 10:37 AM, Jenny Gavin-Wear wrote:
>>
How about:
select b.*
from b
left outer join a on b.id = a.id
where a.id is null
Carl
On 6/21/2011 10:37 AM, Jenny Gavin-Wear wrote:
> Two tables each containing a shared primary key ID.
>
> I am trying to create a query that lists records from table B that are not
> in table A.
>
> Many thanks
Thanks John and Greg :)
>>-Original Message-
>>From: Greg Morphis [mailto:gmorp...@gmail.com]
>>Sent: 21 June 2011 18:45
>>To: cf-talk
>>Subject: Re: SQL Query Problem
>>
>>
>>
>>if your tables are large, you'll probably see
if your tables are large, you'll probably see a better performance from
select id from TableA a
where not exists
(select 1 from TableB b
where a.id = b.id)
On Tue, Jun 21, 2011 at 12:41 PM, John M Bliss wrote:
>
> select * from b where id not in (select id from a)
>
> On Tue, Jun 21, 2011 at 12
select * from b where id not in (select id from a)
On Tue, Jun 21, 2011 at 12:37 PM, Jenny Gavin-Wear <
jenn...@fasttrackonline.co.uk> wrote:
>
> Two tables each containing a shared primary key ID.
>
> I am trying to create a query that lists records from table B that are not
> in table A.
>
> M
>>My problem is with is piece of code in QueryB.
It is creating an ODBC error.
If your query contains quotes, you have to use
#preserveSingleQuotes(SQLQUERY)#
in your query "B"
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/
post the error
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Marc,
YES! - that worked - thank you thank you many times for helping me.
mARK
_
From: Marc Lowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 4:14 PM
To: CF-Talk
Subject: Re: SQL Query problem
This should work.. I hope
Lets get away from the idea of an aggregate
Marc,
YES! - that worked - thank you thank you many times for helping me.
mARK
_
From: Marc Lowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 4:14 PM
To: CF-Talk
Subject: Re: SQL Query problem
This should work.. I hope
Lets get away from the idea of an aggregate
This should work.. I hope
Lets get away from the idea of an aggregate and just use a subquery then
SELECT m.memberID, m.firstname, m.lastname,
t.transactionID as maxTransactionID,
t.paidthru as paidthru
FROM members m
INNER JOIN trans t ON m.memberID = t.memberID
WHERE transactionI
I know why you are getting the results you are getting but I do not have enough information regarding your data to provide a solution yet.
If you add t.paidthru in your group by clause then it will get multiple entries (a cross-join), but if you leave it out you will get invalid
group by errors.
n this one...
_
From: Mark Leder [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 1:09 PM
To: CF-Talk
Subject: RE: SQL Query problem
I'll give this a try and let you know. Thanks for your response.
_
From: Marc Lowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 12:54
I'll give this a try and let you know. Thanks for your response.
_
From: Marc Lowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 12:54 PM
To: CF-Talk
Subject: Re: SQL Query problem
I left a lot of your query out but you should be able to look at this and
see the bi
???
_
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 12:58 PM
To: CF-Talk
Subject: RE: SQL Query problem
All the time you spent searching different solutions you could have
fixed the table strcuture and rewrote the code 5x over. Suck it up Gel
Sorry about that. Wrong list!!!
That was suppose to go to cf-community. :-)
Michael T. Tangorre
> All the time you spent searching different solutions you
> could have fixed the table strcuture and rewrote the code 5x
> over. Suck it up Gel
> :-) Take the high road.
[Todays Threads]
[This
All the time you spent searching different solutions you could have
fixed the table strcuture and rewrote the code 5x over. Suck it up Gel
:-) Take the high road.
Michael T. Tangorre
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
I left a lot of your query out but you should be able to look at this and see the biggest differences.
SELECT m.memberID, m.firstname, m.lastname,
MAX(t.transactionID) as maxID
FROM members m
INNER JOIN trans t ON t.memberID = m.memberID
GROUP BY m.memberID, m.firstname, m.lastname
Hope t
r Sherwood [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 12:15 PM
To: CF-Talk
Subject: RE: SQL Query problem
At 12:05 PM 8/18/2004, you wrote:
>OK, a couple of things:
>1) Below is the complete code for the query page.
SorryI meant the actual SQL code that gets passed
At 12:05 PM 8/18/2004, you wrote:
>OK, a couple of things:
>1) Below is the complete code for the query page.
SorryI meant the actual SQL code that gets passed to the DB. CF will display the actual, parsed SQL code in the debugging output.
Could you post this?
Thanks!
--
Alex
[Todays Threa
fText#%'
with">LIKE '#SESSION.memberList.ffText#%'
=
'#SESSION.memberList.ffText#'
AND '#SESSION.memberList.FilterC#' = M.memberLevelID
AND T.paidThru >= #CreateODBCDate(SESSION.memberLis
At 11:08 AM 8/18/2004, you wrote:
>OK, you asked for it :o) -- the select statement was my latest try at
>retrieving just the highest numbered transaction ID and corresponding data
>for each member. FYI - the filtering statements are only invoked after
>someone does a new search from a form. The
with">LIKE '#SESSION.memberList.ffText#%'
=
'#SESSION.memberList.ffText#'
AND '#SESSION.memberList.FilterC#' = M.memberLevelID
AND T.paidThru >= #CreateODBCDate(SESSION.memberList.DateFrom)#
At 10:14 AM 8/18/2004, you wrote:
>MemberID from the members table corresponds to the memberID in the
>transaction table. So I must be writing the join wrong?
Seems that way. Post the whole query, just as you have it the code.
--
Alex
[Todays Threads]
[This Message]
[Subscription]
[Fast U
MemberID from the members table corresponds to the memberID in the
transaction table. So I must be writing the join wrong?
_
From: Alexander Sherwood [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 10:02 AM
To: CF-Talk
Subject: Re: SQL Query problem
At 10:00 AM 8/18/2004
At 10:00 AM 8/18/2004, you wrote:
>This is giving me fits. In a membership listing, each member could have
>many transactions (one to many relationship). I want to retrieve each member
>ID, and their corresponding most recent (MAX) transactionID. The memberID
>joins the two tables.
>
>I've tried
2001 9:55 AM
To: CF-Talk
Subject: RE: SQL Query Problem
No, you would not want to use isnull(BrandID,0). This would cause the number
of IDs by which to divide the price total to be incorrect. For example, if
you have 10 brands, but 2 of them have NULL fields, the total price should
be divided by
seen that mistake before, and it
wreaks havoc!
Dave.
Original Message Follows
From: "Steven Dworman" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: CF-Talk <[EMAIL PROTECTED]>
Subject: RE: SQL Query Problem
Date: Fri, 16 Nov 2001 08:13:37 -0500
Can't yo
hursday, November 15, 2001 5:49 PM
To: CF-Talk
Subject: Re: SQL Query Problem
I'd add in some AND "WHATEVER BrandIF Field" IS NOT NULL to weed out the
NULL records
Bryan Stevenson
VP & Director of E-Commerce Development
Electric Edge Systems Group I
Try adding:
WHERE BrandID IS NOT NULL
This will eliminate any rows where the value is null. It will have
impact on the calculation of AVG, of course.
If you have multiple BrandID columns, just add "AND BrandID2 IS
NOT NULL", etc.
Stephen
> I am having a problem trying to think of the correc
You can use Coalesce() or ISNULL() to avoid the NULL values, but you'll
still have to keep from including that column in the equation or your
division will come out wrong. i.e. you need to add only those columns that
have values, then divide by the number of columns that had values to get the
aver
I'd add in some AND "WHATEVER BrandIF Field" IS NOT NULL to weed out the
NULL records
Bryan Stevenson
VP & Director of E-Commerce Development
Electric Edge Systems Group Inc.
p. 250.920.8830
e. [EMAIL PROTECTED]
-
Allaire Alliance Partner
ww
--Original Message-
From: Chapman, Katrina <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Thursday, August 17, 2000 3:01 PM
Subject: RE: SQL query problem...
>What's a TB303?
>
>--K
>
>-Original Message-
>From: Rich Wild [mailto:
What's a TB303?
--K
-Original Message-
From: Rich Wild [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 17, 2000 10:57 AM
To: [EMAIL PROTECTED]
Subject: RE: SQL query problem...
OK, this is probably wrong, but its all I can glean from SQL BOL
ALTER TABLE table_name NO
rised representative of e-mango.com ltd.
---
-Original Message-
From: Ryan Williams [mailto:[EMAIL PROTECTED]]
Sent: 17 August 2000 18:15
To: [EMAIL PROTECTED]
Subject: Re: SQL query problem...
Oops. I just stumbled onto the answer by acciden
Thanks for the info. That indeed clarified the problem.
My code now works as intended. :-)
Ryan Williams
[EMAIL PROTECTED]
- Original Message -
From: "Andy Ewings" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 17, 2000 12:59 PM
Subject:
Oops. I just stumbled onto the answer by accident. I just removed the
"-2"
and
associated parentheses from the sub-query and I got my results. My
sub-query
now looks like:
select max(right(wire_id,len(wire_id))) from wire_list)
and it works too !! :-)
My sincerest apologies
If you are using SQL 6.5 then I think I know what's causing this.
This part of your code: (len(wire_id)-2) could well be producing a negative
result and hence when you wrap the right function around it you will get a
536 errorfor more info have a look at what Microsoft say about updat
46 matches
Mail list logo
