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,
Jenny
~|
Order the Adobe Coldfusion Anthology now!
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
Hello,
I am trying to workout a way that will allow me to
run dynamic queries and display the results.
I have created an Access DB table Called QueryTable to
store all my queries in it.
The fileds are: qid (Auto Number), QueryName (Text),
SqlQuery (Memo).I have created a few select/insert queries
post the error
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
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 ways without success. The statement
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 am having a problem trying to think of the correct SQL statement I need.
It's late in the day and my brain is completely fried.
What I am tring to do is get the average price of one column based a an ID
that is passed to the SQL query. For example if a BrandID of 1 is passed
into the query it
www.allaire.com
- Original Message -
From: Brian Ferrigno [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, November 15, 2001 2:39 PM
Subject: SQL Query Problem
I am having a problem trying to think of the correct SQL statement I need.
It's late in the day and my brain
the
average. You should look into using CASE for the conditional logic.
Eric
-Original Message-
From: Brian Ferrigno [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 15, 2001 2:40 PM
To: CF-Talk
Subject: SQL Query Problem
I am having a problem trying to think of the correct SQL statement 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 correct
I have a SQL problem. I know what information I want to retrieve from
the DB,
however, I do not know what precisely is the proper SQL syntax/function is
in order to
achieve it.
The query as it is currently written in my CF template is:
select wl.*,p.*,wlg.*,wb.*,wsnd.*,wrcvd.*,ws.*
To: [EMAIL PROTECTED]
Subject: SQL query problem...
I have a SQL problem. I know what information I want to retrieve from
the DB,
however, I do not know what precisely is the proper SQL syntax/function is
in order to
achieve it.
The query as it is currently written in my CF templat
apologies to everyone in the list.
Ryan Williams
[EMAIL PROTECTED]
- Original Message -
From: "Ryan Williams" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, August 17, 2000 12:45 PM
Subject: SQL query problem...
I have a SQL problem. I know wha
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
I'm trying to do a query where I have data in two different formats...
SELECT Customer, TransNumber, Date
FROM test.Header
Where Customer = "#session.customer#"
The problem I have is that the value of #session.customer# contains the
customer number (260) with no leading characters while the
52 matches
Mail list logo
