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.
Many
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 bliss.j...@gmail.com wrote:
select * from b where id not in (select id from a)
On Tue,
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 a better performance from
select id from TableA a
where not exists
(select 1
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,
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:
~|
That looks familiar! :-)
On Tue, Jun 21, 2011 at 1:09 PM, Stephane Vantroyen s...@emakina.com 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
Carl
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
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
+420
On Tue, Jun 21, 2011 at 2:45 PM, Michael Grant mgr...@modus.bz 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
She didn't provide column names...
On Tue, Jun 21, 2011 at 1:45 PM, Michael Grant mgr...@modus.bz 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
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
Off topic, but the Select * made me shudder
, 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]
Sent: 21 June 2011 19:46
To: cf-talk
Subject: Re: SQL Query Problem
Off topic, but the Select
Hi Michael,
The (very old) web site is about to be completely redeveloped, so I'm really
not too worried.
Appreciate your concern though :)
Jenny
-Original Message-
From: Michael Grant [mailto:mgr...@modus.bz]
Sent: 21 June 2011 23:27
To: cf-talk
Subject: Re: SQL Query Problem
: Michael Grant [mailto:mgr...@modus.bz]
Sent: 21 June 2011 23:27
To: cf-talk
Subject: Re: SQL Query Problem
Right, but if that table grows and columns are added, the overhead on
your
query will grow. Having a small table makes it even easier to define the
columns in your select list
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
post the error
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
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 several
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, you
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
PROTECTED]
Sent: Wednesday, August 18, 2004 10:23 AM
To: CF-Talk
Subject: RE: SQL Query problem
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
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 inital
---
/cfquery
_
From: Alexander Sherwood [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 18, 2004 11:20 AM
To: CF-Talk
Subject: RE: SQL Query problem
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 t
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
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 to the DB. CF will
display the actual, parsed SQL code in the debugging output.
Could you post this?
Thanks
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 that
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
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
??? puzzled
_
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
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 biggest
: 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 PM
To: CF-Talk
Subject
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
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 transactionID = (SELECT
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
, 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 Inc.
p. 250.920.8830
e. [EMAIL PROTECTED
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 you just use isnull(BrandID,0
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 8. However
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
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
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 correct
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
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
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: RE: SQL que
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 accident. I
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 NOCHECK
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:[EMAIL PROTECTED]]
Sent: Thursday, August 17, 2000 10:57
46 matches
Mail list logo
