Re: more of my SQL issues :)
On 12/26/05, John Wilker <[EMAIL PROTECTED]> wrote: > (TypeIDFK = 1 AND Type IN (3,4)) (returns 6,12,15,16,17,77) > (TypeIDFK = 2 AND Type IN (1,5)) (returns 2,3,5,6,9,12,15,20,22) > (TypeIDFK = 3 AND Type IN (3,4)) (returns 6,33,66) > (TypeIDFK = 4 AND Type IN (1,4)) (returns 2,4,6,14,19,20,23,27) > (TypeIDFK = 5 AND Type IN (3,4)) (returns 3,6,7,8,10,12,33) If there are just five TypeIDFK values, you could do this as five consecutive queries. Would that be too horrible? SELECT * FROM typelookup WHERE (TypeIDFK = 1 AND Type IN (3,4)) SELECT * FROM typelookup WHERE (TypeIDFK = 2 AND Type IN (1,5)) AND theKey IN (resultFromQuery1) SELECT * FROM typelookup WHERE (TypeIDFK = 3 AND Type IN (3,4)) AND theKey IN (resultFromQuery2) SELECT * FROM typelookup WHERE (TypeIDFK = 4 AND Type IN (1,4)) AND theKey IN (resultFromQuery3) SELECT * FROM typelookup WHERE (TypeIDFK = 5 AND Type IN (3,4)) AND theKey IN (resultFromQuery4) You'd construct resultFromQueryX as a list from a column of the result of query X. -- Sean A Corfield -- http://corfield.org/ Got frameworks? "If you're not annoying somebody, you're not really alive." -- Margaret Atwood ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227692 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: more of my SQL issues :)
Sure can. This query Select * from typelookup WHERE (TypeIDFK = 1 AND Type IN (3,4)) OR (TypeIDFK = 2 AND Type IN (1,5)) OR (TypeIDFK = 3 AND Type IN (3,4)) OR (TypeIDFK = 4 AND Type IN (1,4)) OR (TypeIDFK = 5 AND Type IN (3,4)) returns TypeIDFKType PotteryIDFK --- -- 4 1 3 2 1 3 2 1 4 5 3 5 2 1 5 1 4 6 2 1 6 3 4 6 4 1 6 5 4 6 5 3 6 3 4 7 2 1 7 2 1 8 2 1 9 this is just the first few rows. YOu can see PotteryID 6 Let me know if I can provide any more info. That's what I figured about needing a one or more subqueries, just not sure how to set something like that up. Thanks! On 12/26/05, Ian Skinner <[EMAIL PROTECTED]> wrote: > > I think this would be a lot easier if you had separate join tables for > each type of join, but I will try and get my head around how this might work > with your universal join table. Can you provide a small representative > example of the data you get if you use the 'OR' version of the WHERE > clause. I will need to set up an example for myself to test ideas. > > I suspect you are correct in that this will most likely require an > interesting sub query or two. > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227691 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: more of my SQL issues :)
I think this would be a lot easier if you had separate join tables for each type of join, but I will try and get my head around how this might work with your universal join table. Can you provide a small representative example of the data you get if you use the 'OR' version of the WHERE clause. I will need to set up an example for myself to test ideas. I suspect you are correct in that this will most likely require an interesting sub query or two. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227690 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: more of my SQL issues :)
What Sean said. I need each of these to (if possible) be something akin to a sub query (I think) (TypeIDFK = 1 AND Type IN (3,4)) (returns 6,12,15,16,17,77) (TypeIDFK = 2 AND Type IN (1,5)) (returns 2,3,5,6,9,12,15,20,22) (TypeIDFK = 3 AND Type IN (3,4)) (returns 6,33,66) (TypeIDFK = 4 AND Type IN (1,4)) (returns 2,4,6,14,19,20,23,27) (TypeIDFK = 5 AND Type IN (3,4)) (returns 3,6,7,8,10,12,33) So in the end I get only that value of (in this case) potteryID = 6, because potteryID 6 is the only one what falls into each of the result sets. Here's a few rows from the look up table that this query needs to run against TypeIDFKType PotteryIDFK --- -- 5 1 3 4 1 3 4 2 3 3 1 3 1 16 3 2 1 3 5 2 4 4 2 4 3 2 4 1 17 4 2 1 4 There are 5 different TypeIDFKs in another lookup (5 different types of data) Hopefully that helps a bit. On 12/26/05, Sean Corfield <[EMAIL PROTECTED]> wrote: > > On 12/26/05, Ben Nadel <[EMAIL PROTECTED]> wrote: > > Maybe I cannot quite imagine how your DB is designed... but I am not > sure > > how it is possible for all of those to return 6 if you AND'ing the > TypeIDFK= > > parts. I am assuming this is an integer field in the DB table - how can > they > > ever equal two different values? > > He is after another field in the record. > > SELECT someKey FROM someTable > WHERE bigComplexConditionInvolvingOtherKeys ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227689 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: more of my SQL issues :)
On 12/26/05, Ben Nadel <[EMAIL PROTECTED]> wrote: > Maybe I cannot quite imagine how your DB is designed... but I am not sure > how it is possible for all of those to return 6 if you AND'ing the TypeIDFK= > parts. I am assuming this is an integer field in the DB table - how can they > ever equal two different values? He is after another field in the record. SELECT someKey FROM someTable WHERE bigComplexConditionInvolvingOtherKeys -- Sean A Corfield -- http://corfield.org/ Got frameworks? "If you're not annoying somebody, you're not really alive." -- Margaret Atwood ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227688 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: more of my SQL issues :)
John, Maybe I cannot quite imagine how your DB is designed... but I am not sure how it is possible for all of those to return 6 if you AND'ing the TypeIDFK= parts. I am assuming this is an integer field in the DB table - how can they ever equal two different values? ... Ben Nadel Web Developer Nylon Technology 6 West 14th Street New York, NY 10011 212.691.1134 212.691.3477 fax www.nylontechnology.com "Vote for Pedro" -Original Message- From: John Wilker [mailto:[EMAIL PROTECTED] Sent: Monday, December 26, 2005 5:15 PM To: CF-Talk Subject: Re: more of my SQL issues :) sorry I wasn't specific. OR won't work because it needs to match all the criteria. There will be items that will match all of the sub clauses. I'm sure I'm not explaining clearly. I need the items that exist at the intersection of each clause; example (TypeIDFK = 1 AND Type IN (3,4)) (returns 6,12,15,16,17,77) (TypeIDFK = 2 AND Type IN (1,5)) (returns 2,3,5,6,9,12,15,20,22) (TypeIDFK = 3 AND Type IN (3,4)) (returns 6,33,66) (TypeIDFK = 4 AND Type IN (1,4)) (returns 2,4,6,14,19,20,23,27) (TypeIDFK = 5 AND Type IN (3,4)) (returns 3,6,7,8,10,12,33) I want to get record 6 On 12/26/05, Ben Nadel <[EMAIL PROTECTED]> wrote: > > Replace your clause joins (AND) with ORs. Right now, you can never get > results cause there is no way that TypeIDFK can equal more than one value. > SQL will parse what you have without parens () since they can be > interchanged and will fail if any of the parts fail. > > Try: > > > Select * from typelookup > WHERE > (TypeIDFK = 1 AND Type IN (3,4)) OR > (TypeIDFK = 2 AND Type IN (1,5)) OR > (TypeIDFK = 3 AND Type IN (3,4)) OR > (TypeIDFK = 4 AND Type IN (1,4)) OR > (TypeIDFK = 5 AND Type IN (3,4)) > > ... > Ben Nadel > Web Developer > Nylon Technology > 6 West 14th Street > New York, NY 10011 > 212.691.1134 > 212.691.3477 fax > www.nylontechnology.com > > "Vote for Pedro" > > -Original Message- > From: John Wilker [mailto:[EMAIL PROTECTED] > Sent: Monday, December 26, 2005 4:32 PM > To: CF-Talk > Subject: Re: more of my SQL issues :) > > Hi All, > > I've continued this thread since my new question is about the same issue, > and about SQL code again. > > I've got this query > > Select * from typelookup > WHERE (TypeIDFK = 1 AND Type IN (3,4)) > AND (TypeIDFK = 2 AND Type IN (1,5)) > AND (TypeIDFK = 3 AND Type IN (3,4)) > AND (TypeIDFK = 4 AND Type IN (1,4)) > AND (TypeIDFK = 5 AND Type IN (3,4)) > > Which returns nothing, I'm not of the SQL syntax to accomplish what I > want. > Basically if I break down each of the (TypeIDFK = 5 AND Type IN (3,4)) > bits > into a seperate query i get a result set of several rows. There's at least > one item that meets each of the where clauses and is in each result set. > Is > there a way to combine all those into ( a working version) of the above > query? > > Thanks all > > J > > On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > > > Ian and Deanna > > > > you guy's approach makes sense. Grouping should get me to the last > > stretch. > > > > Aaron, I'm sure a quick and dirty MSSQL function would work if I had any > > clue as to writing one :) > > > > But I think I can get where I need to be from the two queries provided, > > thanks all. > > > > J > > > > On 12/15/05, Aaron Rouse <[EMAIL PROTECTED]> wrote: > > > > > > I do this in Oracle with a function I wrote. I'd think you could do a > > > quick > > > and dirty function in MSSQL that builds the list to return based off a > > > query > > > to the database and looping over that and concatenating them > all. This > > > is > > > not the method I took in Oracle but seems like it would work. > > > > > > > > > On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > > > > > > > no one? > > > > > > > > > > > > > > > > > > > > > ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227687 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: more of my SQL issues :)
sorry I wasn't specific. OR won't work because it needs to match all the criteria. There will be items that will match all of the sub clauses. I'm sure I'm not explaining clearly. I need the items that exist at the intersection of each clause; example (TypeIDFK = 1 AND Type IN (3,4)) (returns 6,12,15,16,17,77) (TypeIDFK = 2 AND Type IN (1,5)) (returns 2,3,5,6,9,12,15,20,22) (TypeIDFK = 3 AND Type IN (3,4)) (returns 6,33,66) (TypeIDFK = 4 AND Type IN (1,4)) (returns 2,4,6,14,19,20,23,27) (TypeIDFK = 5 AND Type IN (3,4)) (returns 3,6,7,8,10,12,33) I want to get record 6 On 12/26/05, Ben Nadel <[EMAIL PROTECTED]> wrote: > > Replace your clause joins (AND) with ORs. Right now, you can never get > results cause there is no way that TypeIDFK can equal more than one value. > SQL will parse what you have without parens () since they can be > interchanged and will fail if any of the parts fail. > > Try: > > > Select * from typelookup > WHERE > (TypeIDFK = 1 AND Type IN (3,4)) OR > (TypeIDFK = 2 AND Type IN (1,5)) OR > (TypeIDFK = 3 AND Type IN (3,4)) OR > (TypeIDFK = 4 AND Type IN (1,4)) OR > (TypeIDFK = 5 AND Type IN (3,4)) > > ... > Ben Nadel > Web Developer > Nylon Technology > 6 West 14th Street > New York, NY 10011 > 212.691.1134 > 212.691.3477 fax > www.nylontechnology.com > > "Vote for Pedro" > > -Original Message- > From: John Wilker [mailto:[EMAIL PROTECTED] > Sent: Monday, December 26, 2005 4:32 PM > To: CF-Talk > Subject: Re: more of my SQL issues :) > > Hi All, > > I've continued this thread since my new question is about the same issue, > and about SQL code again. > > I've got this query > > Select * from typelookup > WHERE (TypeIDFK = 1 AND Type IN (3,4)) > AND (TypeIDFK = 2 AND Type IN (1,5)) > AND (TypeIDFK = 3 AND Type IN (3,4)) > AND (TypeIDFK = 4 AND Type IN (1,4)) > AND (TypeIDFK = 5 AND Type IN (3,4)) > > Which returns nothing, I'm not of the SQL syntax to accomplish what I > want. > Basically if I break down each of the (TypeIDFK = 5 AND Type IN (3,4)) > bits > into a seperate query i get a result set of several rows. There's at least > one item that meets each of the where clauses and is in each result set. > Is > there a way to combine all those into ( a working version) of the above > query? > > Thanks all > > J > > On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > > > Ian and Deanna > > > > you guy's approach makes sense. Grouping should get me to the last > > stretch. > > > > Aaron, I'm sure a quick and dirty MSSQL function would work if I had any > > clue as to writing one :) > > > > But I think I can get where I need to be from the two queries provided, > > thanks all. > > > > J > > > > On 12/15/05, Aaron Rouse <[EMAIL PROTECTED]> wrote: > > > > > > I do this in Oracle with a function I wrote. I'd think you could do a > > > quick > > > and dirty function in MSSQL that builds the list to return based off a > > > query > > > to the database and looping over that and concatenating them > all. This > > > is > > > not the method I took in Oracle but seems like it would work. > > > > > > > > > On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > > > > > > > no one? > > > > > > > > > > > > > > > > > > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227686 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: more of my SQL issues :)
Replace your clause joins (AND) with ORs. Right now, you can never get results cause there is no way that TypeIDFK can equal more than one value. SQL will parse what you have without parens () since they can be interchanged and will fail if any of the parts fail. Try: Select * from typelookup WHERE (TypeIDFK = 1 AND Type IN (3,4)) OR (TypeIDFK = 2 AND Type IN (1,5)) OR (TypeIDFK = 3 AND Type IN (3,4)) OR (TypeIDFK = 4 AND Type IN (1,4)) OR (TypeIDFK = 5 AND Type IN (3,4)) ... Ben Nadel Web Developer Nylon Technology 6 West 14th Street New York, NY 10011 212.691.1134 212.691.3477 fax www.nylontechnology.com "Vote for Pedro" -Original Message- From: John Wilker [mailto:[EMAIL PROTECTED] Sent: Monday, December 26, 2005 4:32 PM To: CF-Talk Subject: Re: more of my SQL issues :) Hi All, I've continued this thread since my new question is about the same issue, and about SQL code again. I've got this query Select * from typelookup WHERE (TypeIDFK = 1 AND Type IN (3,4)) AND (TypeIDFK = 2 AND Type IN (1,5)) AND (TypeIDFK = 3 AND Type IN (3,4)) AND (TypeIDFK = 4 AND Type IN (1,4)) AND (TypeIDFK = 5 AND Type IN (3,4)) Which returns nothing, I'm not of the SQL syntax to accomplish what I want. Basically if I break down each of the (TypeIDFK = 5 AND Type IN (3,4)) bits into a seperate query i get a result set of several rows. There's at least one item that meets each of the where clauses and is in each result set. Is there a way to combine all those into ( a working version) of the above query? Thanks all J On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > Ian and Deanna > > you guy's approach makes sense. Grouping should get me to the last > stretch. > > Aaron, I'm sure a quick and dirty MSSQL function would work if I had any > clue as to writing one :) > > But I think I can get where I need to be from the two queries provided, > thanks all. > > J > > On 12/15/05, Aaron Rouse <[EMAIL PROTECTED]> wrote: > > > > I do this in Oracle with a function I wrote. I'd think you could do a > > quick > > and dirty function in MSSQL that builds the list to return based off a > > query > > to the database and looping over that and concatenating them all. This > > is > > not the method I took in Oracle but seems like it would work. > > > > > > On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > > > > > no one? > > > > > > > > > > > > ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227685 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: more of my SQL issues :)
Hi All, I've continued this thread since my new question is about the same issue, and about SQL code again. I've got this query Select * from typelookup WHERE (TypeIDFK = 1 AND Type IN (3,4)) AND (TypeIDFK = 2 AND Type IN (1,5)) AND (TypeIDFK = 3 AND Type IN (3,4)) AND (TypeIDFK = 4 AND Type IN (1,4)) AND (TypeIDFK = 5 AND Type IN (3,4)) Which returns nothing, I'm not of the SQL syntax to accomplish what I want. Basically if I break down each of the (TypeIDFK = 5 AND Type IN (3,4)) bits into a seperate query i get a result set of several rows. There's at least one item that meets each of the where clauses and is in each result set. Is there a way to combine all those into ( a working version) of the above query? Thanks all J On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > Ian and Deanna > > you guy's approach makes sense. Grouping should get me to the last > stretch. > > Aaron, I'm sure a quick and dirty MSSQL function would work if I had any > clue as to writing one :) > > But I think I can get where I need to be from the two queries provided, > thanks all. > > J > > On 12/15/05, Aaron Rouse <[EMAIL PROTECTED]> wrote: > > > > I do this in Oracle with a function I wrote. I'd think you could do a > > quick > > and dirty function in MSSQL that builds the list to return based off a > > query > > to the database and looping over that and concatenating them all. This > > is > > not the method I took in Oracle but seems like it would work. > > > > > > On 12/15/05, John Wilker <[EMAIL PROTECTED]> wrote: > > > > > > no one? > > > > > > > > > > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227684 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: help with paypal ipn testing - always INVALID response
Thanks, I'm aware of it and thought of it, but the code to do this IPN is only a few lines long. I think it must work and is just something silly; going to rig it up so I can try it live and see... ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:227683 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
