House of Fusion
Search over 2,500 ColdFusion resources here
  
Home of the ColdFusion Community

Mailing Lists
Home /  Groups /  ColdFusion Newbie (CF-Newbie)

ColdFusion -NEWBIE: SQL subquery issues

  << Previous Post |  RSS |  Tree View |  Sort Latest First |  Subscribe to this Group Next >> 

CF-NEWBIE: SQL subquery issues

Dear all, Martin, Donna M 02/02/2012 08:41 AM
You might try this in TWO queries - Eric Nicholas Sweeney 02/02/2012 02:01 PM
Yes, I do get the idea, and the logic looks good.  I won't have the Martin, Donna M 02/02/2012 02:23 PM
Donna, Phillips, Dave 02/02/2012 02:59 PM
Actually, I am using SQL Server, but am relatively new to it.  I'll Martin, Donna M 02/02/2012 03:10 PM

02/02/2012 08:41 AM
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).
02/02/2012 02:01 PM
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).
02/02/2012 02:23 PM
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).
02/02/2012 02:59 PM
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).
02/02/2012 03:10 PM
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).
<< Previous Thread Today's Threads Next Thread >>

Search cf-newbie

December 18, 2014

<<   <   Today   >   >>
Su Mo Tu We Th Fr Sa
   1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31       

Designer, Developer and mobile workflow conference