Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Newbie (CF-Newbie)
ColdFusion -NEWBIE: SQL subquery issues
Author: Martin, Donna M
Short Link: http://www.houseoffusion.com/groups/cf-newbie/thread.cfm/threadid:1322#5787
Actually, I am using SQL Server, but am relatively new to it. I'll let you know
how it goes.
Thanks much for sending this tidbit, which I did not know...
Donna
Donna,
I know in SQL Server and other database you can use NOT IN instead of <>.
I am going to assume you can do the same in Access (although that is not always a
valid assumption to make!):
SELECT T.TblName_seq, Email, FName, MName, LName, JrSr, Degree, P.ForYear from
(TblName T LEFT JOIN Payment P
ON T.Tblname_seq=P.TblName_seq)
WHERE T.TblName_seq NOT IN (SELECT TblName_seq From Payment WHERE
ForYear=2012)
And that should do it.
Dave
Dear all,
Okay...It's been a very rough January, and am having a minor brain meltdown. I'm
just not coming up with the solution for the following the scenario:
I have a PAYMENT table that has a record for each year paid. All I want to do is
come up with a listing of names with email's in the MEMBER table who have not
paid for 2012.
I'm about to reveal my feable attempt, so here's my code at this point:
<cfquery name="Unpaid2012" datasource="#datasource#">
SELECT T.TblName_seq, Email, FName, MName, LName, JrSr, Degree, P.ForYear from
(TblName T
LEFT JOIN Payment P
ON T.Tblname_seq=P.TblName_seq)
WHERE T.TblName_seq <> (SELECT TblName_seq From Payment WHERE
ForYear=2012)
</cfquery>
Obviously, I cannot have the '<>' operator in a subquery with multiple
returns, yes? But how do I fix this?
I would great appreciate it if anyone could help out...
Thanks in advance.
Donna
________________________________
This e-mail message (including any attachments) is for the sole use of
the intended recipient(s) and may contain confidential and privileged
information. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, distribution
or copying of this message (including any attachments) is strictly
prohibited.
If you have received this message in error, please contact
the sender by reply e-mail message and destroy all copies of the
original message (including attachments).
Author: Phillips, Dave
Short Link: http://www.houseoffusion.com/groups/cf-newbie/thread.cfm/threadid:1322#5786
Donna,
I know in SQL Server and other database you can use NOT IN instead of <>.
I am going to assume you can do the same in Access (although that is not always a
valid assumption to make!):
SELECT T.TblName_seq, Email, FName, MName, LName, JrSr, Degree, P.ForYear from
(TblName T
LEFT JOIN Payment P
ON T.Tblname_seq=P.TblName_seq)
WHERE T.TblName_seq NOT IN (SELECT TblName_seq From Payment WHERE
ForYear=2012)
And that should do it.
Dave
Dear all,
Okay...It's been a very rough January, and am having a minor brain meltdown. I'm
just not coming up with the solution for the following the scenario:
I have a PAYMENT table that has a record for each year paid. All I want to do is
come up with a listing of names with email's in the MEMBER table who have not
paid for 2012.
I'm about to reveal my feable attempt, so here's my code at this point:
<cfquery name="Unpaid2012" datasource="#datasource#">
SELECT T.TblName_seq, Email, FName, MName, LName, JrSr, Degree, P.ForYear from
(TblName T
LEFT JOIN Payment P
ON T.Tblname_seq=P.TblName_seq)
WHERE T.TblName_seq <> (SELECT TblName_seq From Payment WHERE
ForYear=2012)
</cfquery>
Obviously, I cannot have the '<>' operator in a subquery with multiple
returns, yes? But how do I fix this?
I would great appreciate it if anyone could help out...
Thanks in advance.
Donna
________________________________
This e-mail message (including any attachments) is for the sole use of
the intended recipient(s) and may contain confidential and privileged
information. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, distribution
or copying of this message (including any attachments) is strictly
prohibited.
If you have received this message in error, please contact
the sender by reply e-mail message and destroy all copies of the
original message (including attachments).
Author: Martin, Donna M
Short Link: http://www.houseoffusion.com/groups/cf-newbie/thread.cfm/threadid:1322#5785
Yes, I do get the idea, and the logic looks good. I won't have the opportunity
to test it out until this evening, but this 'should' do the trick. I will
probably add a WHERE statement in the first query -- something like, WHERE
ForYear=2012.
Thanks, Nick...really.
You might try this in TWO queries -
Query One - Get the MemberIDs (Or Emails) of people who paid
<cfquery name=" MyQry " datasource="#Application.DSN#"
username="#Application.username#" password="#Application.password#">
SELECT MemberID
FROM PaymentTable </cfquery>
Save those Member IDs to a List <cfset variables.MemberIDList =
ValueList(MyQry.MemberID)>
Query Two Get Members whose ID NOT IN (variables.MemberIDList)
<cfquery name="QryNonPayers" datasource="#Application.DSN#"
username="#Application.username#" password="#Application.password#">
SELECT Email
FROM MemberTable
WHERE MemberID NOT IN (variables.MemberIDList)
</cfquery>
Not tested - but you should get the idea...
- Nick
Dear all,
Okay...It's been a very rough January, and am having a minor brain meltdown.
I'm just not coming up with the solution for the following the scenario:
I have a PAYMENT table that has a record for each year paid. All I want to
do is come up with a listing of names with email's in the MEMBER table who
have not paid for 2012.
I'm about to reveal my feable attempt, so here's my code at this point:
<cfquery name="Unpaid2012" datasource="#datasource#"> SELECT
T.TblName_seq,
Email, FName, MName, LName, JrSr, Degree, P.ForYear from (TblName T LEFT
JOIN Payment P
ON T.Tblname_seq=P.TblName_seq)
WHERE T.TblName_seq <> (SELECT TblName_seq From Payment WHERE
ForYear=2012) </cfquery>
Obviously, I cannot have the '<>' operator in a subquery with multiple
returns, yes? But how do I fix this?
I would great appreciate it if anyone could help out...
Thanks in advance.
Donna
________________________________
This e-mail message (including any attachments) is for the sole use of the
intended recipient(s) and may contain confidential and privileged
information. If the reader of this message is not the intended recipient,
you are hereby notified that any dissemination, distribution or copying of
this message (including any attachments) is strictly prohibited.
If you have received this message in error, please contact the sender by
reply e-mail message and destroy all copies of the original message
(including attachments).
Author: Eric Nicholas Sweeney
Short Link: http://www.houseoffusion.com/groups/cf-newbie/thread.cfm/threadid:1322#5784
You might try this in TWO queries -
Query One - Get the MemberIDs (Or Emails) of people who paid
<cfquery name=" MyQry " datasource="#Application.DSN#"
username="#Application.username#" password="#Application.password#">
SELECT MemberID
FROM PaymentTable
</cfquery>
Save those Member IDs to a List <cfset variables.MemberIDList =
ValueList(MyQry.MemberID)>
Query Two Get Members whose ID NOT IN (variables.MemberIDList)
<cfquery name="QryNonPayers" datasource="#Application.DSN#"
username="#Application.username#" password="#Application.password#">
SELECT Email
FROM MemberTable
WHERE MemberID NOT IN (variables.MemberIDList)
</cfquery>
Not tested - but you should get the idea...
- Nick
Dear all,
Okay...It's been a very rough January, and am having a minor brain meltdown.
I'm just not coming up with the solution for the following the scenario:
I have a PAYMENT table that has a record for each year paid. All I want to
do is come up with a listing of names with email's in the MEMBER table who
have not paid for 2012.
I'm about to reveal my feable attempt, so here's my code at this point:
<cfquery name="Unpaid2012" datasource="#datasource#"> SELECT
T.TblName_seq,
Email, FName, MName, LName, JrSr, Degree, P.ForYear from (TblName T LEFT
JOIN Payment P
ON T.Tblname_seq=P.TblName_seq)
WHERE T.TblName_seq <> (SELECT TblName_seq From Payment WHERE
ForYear=2012) </cfquery>
Obviously, I cannot have the '<>' operator in a subquery with multiple
returns, yes? But how do I fix this?
I would great appreciate it if anyone could help out...
Thanks in advance.
Donna
________________________________
This e-mail message (including any attachments) is for the sole use of the
intended recipient(s) and may contain confidential and privileged
information. If the reader of this message is not the intended recipient,
you are hereby notified that any dissemination, distribution or copying of
this message (including any attachments) is strictly prohibited.
If you have received this message in error, please contact the sender by
reply e-mail message and destroy all copies of the original message
(including attachments).
Author: Martin, Donna M
Short Link: http://www.houseoffusion.com/groups/cf-newbie/thread.cfm/threadid:1322#5783
Dear all,
Okay...It's been a very rough January, and am having a minor brain meltdown. I'm
just not coming up with the solution for the following the scenario:
I have a PAYMENT table that has a record for each year paid. All I want to do is
come up with a listing of names with email's in the MEMBER table who have not
paid for 2012.
I'm about to reveal my feable attempt, so here's my code at this point:
<cfquery name="Unpaid2012" datasource="#datasource#">
SELECT T.TblName_seq, Email, FName, MName, LName, JrSr, Degree, P.ForYear from
(TblName T
LEFT JOIN Payment P
ON T.Tblname_seq=P.TblName_seq)
WHERE T.TblName_seq <> (SELECT TblName_seq From Payment WHERE
ForYear=2012)
</cfquery>
Obviously, I cannot have the '<>' operator in a subquery with multiple
returns, yes? But how do I fix this?
I would great appreciate it if anyone could help out...
Thanks in advance.
Donna
________________________________
This e-mail message (including any attachments) is for the sole use of
the intended recipient(s) and may contain confidential and privileged
information. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, distribution
or copying of this message (including any attachments) is strictly
prohibited.
If you have received this message in error, please contact
the sender by reply e-mail message and destroy all copies of the
original message (including attachments).
|
June 19, 2013
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
