RE: SQL Help Needed
This isn't how it works (or how BarneyB explained it). The statement is always INSERT INTO, followed by either VALUES or a SELECT statement. The select statement can have any result, as long as it has the same number of columns and the column datatypes match. So, your statement should probably look like this: INSERT INTO tblProducts (ProdName, ProdDesc, ProdSRP, ProdPrice, ProdCost, SubcatID) SELECT name, description, srp, price, cost, subcategoryID FROM dolls_backup Pascal -Original Message- From: Donna French [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 23:13 To: CF-Talk Subject: Re: SQL Help Needed Okay, here's what I've come up with we'll see how it goes... SELECT lanesID, subcategoryID, name, availability, price, srp, cost, description FROM dolls_backup INSERT INTO tblProducts(ProdName, ProdDesc, ProdSRP, ProdPrice, ProdCost, SubcatID) VALUES (dolls_backup.name, dolls_backup.description, dolls_backup.srp, dolls_backup.cost, dolls_backup.subcategoryID) ~ Donna ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184846 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: SQL Help Needed
You'll have to check on the exact syntax, but something like this should work: INSERT INTO myTable (col1, col2, col3) SELECT col4, col5, col6 FROM otherTable The SELECT can be as complex as you want, as long as the columns it returns are the same number and type as what is needed by the INSERT portion. cheers, barneyb On Thu, 18 Nov 2004 15:33:40 -0600, Donna French [EMAIL PROTECTED] wrote: I am redesigning an SQL database and need to know if it's possible to select specific tables/columns from the original into specific tables/columns in the new database. I know I can select the columns I want but moving them into a specified column in the new db that isn't the same structure is what I'm not sure of. Any help appreciated. -- -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/blog/ ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184820 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: SQL Help Needed
Okay, here's what I've come up with we'll see how it goes... SELECT lanesID, subcategoryID, name, availability, price, srp, cost, description FROM dolls_backup INSERT INTO tblProducts(ProdName, ProdDesc, ProdSRP, ProdPrice, ProdCost, SubcatID) VALUES (dolls_backup.name, dolls_backup.description, dolls_backup.srp, dolls_backup.cost, dolls_backup.subcategoryID) ~ Donna On Thu, 18 Nov 2004 13:49:06 -0800, Barney Boisvert [EMAIL PROTECTED] wrote: You'll have to check on the exact syntax, but something like this should work: INSERT INTO myTable (col1, col2, col3) SELECT col4, col5, col6 FROM otherTable The SELECT can be as complex as you want, as long as the columns it returns are the same number and type as what is needed by the INSERT portion. cheers, barneyb On Thu, 18 Nov 2004 15:33:40 -0600, Donna French [EMAIL PROTECTED] wrote: I am redesigning an SQL database and need to know if it's possible to select specific tables/columns from the original into specific tables/columns in the new database. I know I can select the columns I want but moving them into a specified column in the new db that isn't the same structure is what I'm not sure of. Any help appreciated. -- -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/blog/ ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184823 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: SQL help needed fast
On Friday 05 Nov 2004 21:00 pm, Eric Creese wrote: lines is an int and this is MySQL Database. No issues with it in access or sql server Are those escaped ' meant to be there, or did it just happen when you pasted into your mail client ? What happens if you try the query by hand ? also in an order by clause in MySQl how many columns can be sorted at one time? Lots. -- Tom Chiverton Advanced ColdFusion Programmer Tel: +44 (0)1749 834900 email: [EMAIL PROTECTED] BlueFinger Limited Underwood Business Park Wookey Hole Road, WELLS. BA5 1AF Tel: +44 (0)1749 834900 Fax: +44 (0)1749 834XXX web: www.bluefinger.com Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple Quay, BRISTOL. BS1 6EG *** This E-mail contains confidential information for the addressee only. If you are not the intended recipient, please notify us immediately. You should not use, disclose, distribute or copy this communication if received in error. No binding contract will result from this e-mail until such time as a written document is signed on behalf of the company. BlueFinger Limited cannot accept responsibility for the completeness or accuracy of this message as it has been transmitted over public networks.*** ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183603 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: SQL help needed fast
I am not sure by what you mean about escaping but that was the read out from the error page. SELECT * FROM p1_matrix WHERE display=apos;yesapos; AND lines = 1 ORDER BY Charht ; I have done this successfully with Access. I did notice that the datatype for charht is decimal and for lines it is int, so I am not sure if that is the problem or not. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183623 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: SQL help needed fast
which db, what is the datatype of lines?
On Fri, 5 Nov 2004 14:34:46 -0600, Eric Creese [EMAIL PROTECTED] wrote:
Can some one please tell me why this does not work? If you pass the url.inch
variable it works fine. If you pass the url.line variable it fails with the
following error. code is below the error I need an answer quick please
Syntax error or access violation: You have an error in your SQL syntax near 'lines =
1 ORDER BY Charht ' at line 8 The error occurred in
/home/auto1/autocomm-inc.com/html/dev/matrix.cfm: line 3
1 : cfinclude template=header2.cfm
2 :
3 : cfquery name=getmatrix datasource=yeuwes
4 : SELECT *
5 : FROM p#url.prodid#_matrix
SELECT * FROM p1_matrix WHERE display=apos;yesapos; AND lines = 1 ORDER BY Charht ;
cfquery name=getmatrix datasource=autocomm
SELECT *
FROM p#url.prodid#_matrix
WHERE display='yes'
cfif url.prodid EQ 1
cfif isdefined('url.inch')
cfif url.inch EQ 1
AND charht 2
cfelseif url.inch EQ 2
AND charht 3
AND charht =2
cfelseif url.inch EQ 3
AND charht 4
AND charht =3
cfelseif url.inch EQ 4
AND charht 7
AND charht =4
cfelseif url.inch EQ 7
AND charht =7
/cfif
/cfif
cfif isdefined('url.line')
cfif url.line EQ 1
AND lines = 1
cfelseif url.line EQ 2
AND lines =2
cfelseif url.line EQ 3
AND lines =3
cfelseif url.line EQ 4
AND lines =4
/cfif
/cfif
ORDER BY Charht
cfelseif url.prodid EQ 6
cfif isdefined('url.inch')
cfif url.inch EQ 5
AND charht 6
cfelseif url.inch EQ 8
AND charht 9
AND charht =6
cfelseif url.inch EQ 18
AND charht 19
AND charht =9
cfelseif url.inch EQ 29
AND charht 30
AND charht =19
cfelseif url.inch EQ 30
AND charht =30
/cfif
/cfif
cfif isdefined('url.line')
cfif url.line EQ 1
AND lines = 1
cfelseif url.line EQ 2
AND lines =2
cfelseif url.line EQ 3
AND lines =3
cfelseif url.line EQ 4
AND lines =4
/cfif
/cfif
ORDER BY charht
cfelseif url.prodid EQ 19
cfif isdefined('url.type')
cfif url.type EQ 'baseball'
AND event ='baseball'
cfelseif url.type EQ 'basketball'
AND (event = 'basketball'
OR event='Bsktbll/Wrstlng/Vollyball'
OR event='Possession Indicator'
OR event='Foul Panels'
OR event='Shot Clock')
cfelseif url.type EQ 'football'
AND event ='football'
cfelseif url.type EQ 'hockey'
AND event ='hockey'
cfelseif url.type EQ 'BWV'
AND event ='Bsktbll/Wrstlng/Vollyball'
cfelseif url.type EQ 'race'
AND event ='race track'
cfelseif url.type EQ 'multi'
AND event ='multi-sport'
cfelseif url.type EQ 'soccer'
AND event ='soccer'
/cfif
/cfif
ORDER BY event
/cfif
;
/cfquery
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183541
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: SQL help needed fast
lines is an int and this is MySQL Database. No issues with it in access or sql server
also in an order by clause in MySQl how many columns can be sorted at one time?
-Original Message-
From: Qasim Rasheed [mailto:[EMAIL PROTECTED]
Sent: Friday, November 05, 2004 2:49 PM
To: CF-Talk
Subject: Re: SQL help needed fast
which db, what is the datatype of lines?
On Fri, 5 Nov 2004 14:34:46 -0600, Eric Creese [EMAIL PROTECTED] wrote:
Can some one please tell me why this does not work? If you pass the url.inch
variable it works fine. If you pass the url.line variable it fails with the
following error. code is below the error I need an answer quick please
Syntax error or access violation: You have an error in your SQL syntax near 'lines =
1 ORDER BY Charht ' at line 8 The error occurred in
/home/auto1/autocomm-inc.com/html/dev/matrix.cfm: line 3
1 : cfinclude template=header2.cfm
2 :
3 : cfquery name=getmatrix datasource=yeuwes
4 : SELECT *
5 : FROM p#url.prodid#_matrix
SELECT * FROM p1_matrix WHERE display=apos;yesapos; AND lines = 1 ORDER BY Charht ;
cfquery name=getmatrix datasource=autocomm
SELECT *
FROM p#url.prodid#_matrix
WHERE display='yes'
cfif url.prodid EQ 1
cfif isdefined('url.inch')
cfif url.inch EQ 1
AND charht 2
cfelseif url.inch EQ 2
AND charht 3
AND charht =2
cfelseif url.inch EQ 3
AND charht 4
AND charht =3
cfelseif url.inch EQ 4
AND charht 7
AND charht =4
cfelseif url.inch EQ 7
AND charht =7
/cfif
/cfif
cfif isdefined('url.line')
cfif url.line EQ 1
AND lines = 1
cfelseif url.line EQ 2
AND lines =2
cfelseif url.line EQ 3
AND lines =3
cfelseif url.line EQ 4
AND lines =4
/cfif
/cfif
ORDER BY Charht
cfelseif url.prodid EQ 6
cfif isdefined('url.inch')
cfif url.inch EQ 5
AND charht 6
cfelseif url.inch EQ 8
AND charht 9
AND charht =6
cfelseif url.inch EQ 18
AND charht 19
AND charht =9
cfelseif url.inch EQ 29
AND charht 30
AND charht =19
cfelseif url.inch EQ 30
AND charht =30
/cfif
/cfif
cfif isdefined('url.line')
cfif url.line EQ 1
AND lines = 1
cfelseif url.line EQ 2
AND lines =2
cfelseif url.line EQ 3
AND lines =3
cfelseif url.line EQ 4
AND lines =4
/cfif
/cfif
ORDER BY charht
cfelseif url.prodid EQ 19
cfif isdefined('url.type')
cfif url.type EQ 'baseball'
AND event ='baseball'
cfelseif url.type EQ 'basketball'
AND (event = 'basketball'
OR event='Bsktbll/Wrstlng/Vollyball'
OR event='Possession Indicator'
OR event='Foul Panels'
OR event='Shot Clock')
cfelseif url.type EQ 'football'
AND event ='football'
cfelseif url.type EQ 'hockey'
AND event ='hockey'
cfelseif url.type EQ 'BWV'
AND event ='Bsktbll/Wrstlng/Vollyball'
cfelseif url.type EQ 'race'
AND event ='race track'
cfelseif url.type EQ 'multi'
AND event ='multi-sport'
cfelseif url.type EQ 'soccer'
AND event ='soccer'
/cfif
/cfif
ORDER BY event
/cfif
;
/cfquery
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183545
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: SQL Help Needed!
This will work in sql server. Don't think access supports compound outer joins... I could be mistaken though... Select * from Docmaster left outer join versions on docmaster.docnum = versions.docnumber and docmaster.version = versions.version Where verersions.docnumber is null -Original Message- From: Gieseman, Athelene [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 12:38 PM To: CF-Talk Subject: OT:SQL Help Needed! I just went through a conversion from one product to another. As a part of that process, records were exported via CSV and imported into another MS SQL database. Somewhere along the way, not all of the records were imported. (It was done using the vendor's import utility which was useless in troubleshooting this issue.) The question is this: I want to find all of the records in the original DB that did not make it into the new one. There are only two tables involved. We copied the original table into the new database to make it easier. So now they are both in the same DB. The original table is called DOCMASTER. The new table is called VERSIONS. I need to find all of the records in DOCMASTER that do not exist in VERSIONS. The only way I can tell is from two fields in each. DOCMASTER.DOCNUM should equal VERSIONS.DOCNUMBER And DOCMASTER.VERSION should equal VERSIONS.VERSION How can I write a query to show me every record in DOCMASTER That didn't make it into VERSIONS? I know this should be easy. I apologize in advance. Very sleep deprived at this point. Any help is appreciated. Athelene Gieseman [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Help Needed!
UH OH! THERE IS A PROBLEM WITH WHAT I'M ASSUMING CAME FROM THE CFHUB SITE. I JUST TRIED TO SIGN UP AND NOW I AM GETTING ALL THESE EMAILS! -Original Message- From: Costas Piliotis [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 3:51 PM To: CF-Talk Subject: RE: SQL Help Needed! This will work in sql server. Don't think access supports compound outer joins... I could be mistaken though... Select * from Docmaster left outer join versions on docmaster.docnum = versions.docnumber and docmaster.version = versions.version Where verersions.docnumber is null -Original Message- From: Gieseman, Athelene [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 12:38 PM To: CF-Talk Subject: OT:SQL Help Needed! I just went through a conversion from one product to another. As a part of that process, records were exported via CSV and imported into another MS SQL database. Somewhere along the way, not all of the records were imported. (It was done using the vendor's import utility which was useless in troubleshooting this issue.) The question is this: I want to find all of the records in the original DB that did not make it into the new one. There are only two tables involved. We copied the original table into the new database to make it easier. So now they are both in the same DB. The original table is called DOCMASTER. The new table is called VERSIONS. I need to find all of the records in DOCMASTER that do not exist in VERSIONS. The only way I can tell is from two fields in each. DOCMASTER.DOCNUM should equal VERSIONS.DOCNUMBER And DOCMASTER.VERSION should equal VERSIONS.VERSION How can I write a query to show me every record in DOCMASTER That didn't make it into VERSIONS? I know this should be easy. I apologize in advance. Very sleep deprived at this point. Any help is appreciated. Athelene Gieseman [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Help Needed!
Worked perfectly! Thank you! Now if I can only get them imported. Athelene -Original Message- From: Costas Piliotis [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 2:51 PM To: CF-Talk Subject: RE: SQL Help Needed! This will work in sql server. Don't think access supports compound outer joins... I could be mistaken though... Select * from Docmaster left outer join versions on docmaster.docnum = versions.docnumber and docmaster.version = versions.version Where verersions.docnumber is null -Original Message- From: Gieseman, Athelene [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 12:38 PM To: CF-Talk Subject: OT:SQL Help Needed! I just went through a conversion from one product to another. As a part of that process, records were exported via CSV and imported into another MS SQL database. Somewhere along the way, not all of the records were imported. (It was done using the vendor's import utility which was useless in troubleshooting this issue.) The question is this: I want to find all of the records in the original DB that did not make it into the new one. There are only two tables involved. We copied the original table into the new database to make it easier. So now they are both in the same DB. The original table is called DOCMASTER. The new table is called VERSIONS. I need to find all of the records in DOCMASTER that do not exist in VERSIONS. The only way I can tell is from two fields in each. DOCMASTER.DOCNUM should equal VERSIONS.DOCNUMBER And DOCMASTER.VERSION should equal VERSIONS.VERSION How can I write a query to show me every record in DOCMASTER That didn't make it into VERSIONS? I know this should be easy. I apologize in advance. Very sleep deprived at this point. Any help is appreciated. Athelene Gieseman [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL Help Needed!
CF-Talk emails from the 'Hub? Perhaps you subscribed to CF-Talk? I know CFHub posted another link to CF-Talk (co houseoffusion.com) today but... UH OH! THERE IS A PROBLEM WITH WHAT I'M ASSUMING CAME FROM THE CFHUB SITE. I JUST TRIED TO SIGN UP AND NOW I AM GETTING ALL THESE EMAILS! __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL help needed - Apples but not Oranges...
Merry Meet Ron, Have you tried? SELECT DISTINCT Clients.id, Clients.firstname, Clients.lastname FROM Clients,Orders,OrderItems,Products WHERE Clients.id = Orders.clientid AND Orders.id = OrderItems.orderid AND OrderItems.productid = Products.id AND Products.name LIKE '%Apples%' AND Products.name NOT LIKE '%Oranges%' Blessed Be, --Katrina Chapman http://www.katrinachapman.com http://www.cfchick.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, January 19, 2001 7:51 PM To: CF-Talk Subject: SQL help needed - Apples but not Oranges... I'm trying to select clients who have purchased "Apples", but NOT "Oranges". This query is returning clients who have bought either. SELECT DISTINCT Clients.id, Clients.firstname, Clients.lastname FROM Clients,Orders,OrderItems,Products WHERE ( Clients.id = Orders.clientid AND Orders.id = OrderItems.orderid AND OrderItems.productid = Products.id AND Products.name LIKE '%Apples%' ) AND ( Clients.id = Orders.clientid AND Orders.id = OrderItems.orderid AND OrderItems.productid = Products.id AND Products.name NOT LIKE '%Oranges%' ) Any help is most appreciated! -ron ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL help needed - Apples but not Oranges (SOLVED)...
Got the answer on this one already, from a list that doesn't take over 4 hours for posts to appear. Here it is: SELECT DISTINCT Clients.id, Clients.firstname, Clients.lastname FROM Clients,Orders,OrderItems,Products WHERE ( Clients.id = Orders.clientid AND Orders.id = OrderItems.orderid AND OrderItems.productid = Products.id AND Products.name LIKE '%Apples%' ) AND Clients.id not in ( SELECT Clients.id FROM Clients,Orders,OrderItems,Products WHERE Clients.id = Orders.clientid AND Orders.id = OrderItems.orderid AND OrderItems.productid = Products.id AND Products.name LIKE '%Oranges%' ) Thanks to all who have probably already responded here! -ron -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, January 19, 2001 1:51 PM To: CF-Talk Subject: SQL help needed - Apples but not Oranges... I'm trying to select clients who have purchased "Apples", but NOT "Oranges". This query is returning clients who have bought either. SELECT DISTINCT Clients.id, Clients.firstname, Clients.lastname FROM Clients,Orders,OrderItems,Products WHERE ( Clients.id = Orders.clientid AND Orders.id = OrderItems.orderid AND OrderItems.productid = Products.id AND Products.name LIKE '%Apples%' ) AND ( Clients.id = Orders.clientid AND Orders.id = OrderItems.orderid AND OrderItems.productid = Products.id AND Products.name NOT LIKE '%Oranges%' ) Any help is most appreciated! -ron ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL help needed - Apples but not Oranges...
I take it you want to exlude a word from a search. Valid names: Apple Time Sauce and Apples Invalid: Bob's Donuts Oranges and Apples Apples the colour of Orange Bananas Oranges SELECT DISTINCT c.id, c.firstname, c.lastname FROMClients c, Orders o, OrderItems i, Products p WHERE c.id = o.id AND o.id = i.orderid AND i.productid = p.id AND ( p.name LIKE '%Apples%' AND p.name NOT LIKE '%Oranges%' ) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: January 19, 2001 14:51 To: CF-Talk Subject: SQL help needed - Apples but not Oranges... I'm trying to select clients who have purchased "Apples", but NOT "Oranges". This query is returning clients who have bought either. SELECT DISTINCT Clients.id, Clients.firstname, Clients.lastname FROM Clients,Orders,OrderItems,Products WHERE ( Clients.id = Orders.clientid AND Orders.id = OrderItems.orderid AND OrderItems.productid = Products.id AND Products.name LIKE '%Apples%' ) AND ( Clients.id = Orders.clientid AND Orders.id = OrderItems.orderid AND OrderItems.productid = Products.id AND Products.name NOT LIKE '%Oranges%' ) Any help is most appreciated! -ron ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
