RE: Db query help needed
You cannot retrieve multiple records in a sub-query, only one. You want SELECT DISTINCT A.itemID, B.CategoryID FROM items A INNER JOIN Categories B ON A.ItemID = B.itemID WHERE A.itemID = If you only want category ID's and nothing else, try: SELECT DISTINCT categoryID FROM categories Then just loop through and only output the categoryID field. -Original Message- From: DKI [mailto:[EMAIL PROTECTED] Sent: Thursday, May 10, 2007 3:20 PM To: CF-Talk Subject: Db query help needed Hi, I have a sql query like this that works fine: SELECT itemID, (SELECT COUNT (DISTINCT categoryid) FROM categories WHERE itemidID = items.itemID) AS categorycount FROM items But what i need is not the count, but the actual list of categories, as one column, in a single subquery. I tried the query below and obviously got an error message. But still i am giving it you to show what i need. Any ideas guys? Db used: ms sql 2000. Please help. Here's what i am using: SELECT itemID, (SELECT categoryid FROM categories WHERE itemidID = items.itemID) AS categoryidList FROM items Thanks, K ~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277664 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: db query
update yourtable set emailCol = substring(emailCol,2,LEN(emailCol)) where emailCol like '\'%' Now you need to pin down that where clause first, with a select statement to make sure you match the correct rows. I don't use MS SQL so I'm not sure how you escape a single quote. Someone on the list will probably fill that part in. On 5/24/06, Ken <[EMAIL PROTECTED]> wrote: > > Thanks Brian. But what i am looking for is an update statement to > strip all the single quotes from the emailaddress column. > > - K > > On 5/24/06, Brian Polackoff <[EMAIL PROTECTED]> wrote: > > You can try.. > > > > select substring(emailCol,2,LEN(emailCol)) from tablename > > > > Hope it helps! > > > > -Brian- > > > > > > > > > > -Original Message- > > From: Ken [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, May 24, 2006 2:05 PM > > To: CF-Talk > > Subject: db query > > > > Hii. I have a number of email addresses in my db that start with a > > single quote. Like this: '[EMAIL PROTECTED] > > '[EMAIL PROTECTED] > > > > etc. > > > > I need to run a query to strip off all the ' single quotes out of the > > email addresses. I am using MS SQL 2000. > > > > Please help. > > > > > > > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241338 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: db query
Ken... Brian helped you out, you just have to do a little query writing... Update mytable Set email = substring(email,2,len(email)) Where email LIKE '''%' Since a single quote is never a part of an amail you could also do... Set email = replace(email,'''','') -Mark -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 1:17 PM To: CF-Talk Subject: Re: db query Thanks Brian. But what i am looking for is an update statement to strip all the single quotes from the emailaddress column. - K On 5/24/06, Brian Polackoff <[EMAIL PROTECTED]> wrote: > You can try.. > > select substring(emailCol,2,LEN(emailCol)) from tablename > > Hope it helps! > > -Brian- > > > > > -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 24, 2006 2:05 PM > To: CF-Talk > Subject: db query > > Hii. I have a number of email addresses in my db that start with a > single quote. Like this: '[EMAIL PROTECTED] '[EMAIL PROTECTED] > > etc. > > I need to run a query to strip off all the ' single quotes out of the > email addresses. I am using MS SQL 2000. > > Please help. > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241335 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: db query
Thanks Brian. But what i am looking for is an update statement to strip all the single quotes from the emailaddress column. - K On 5/24/06, Brian Polackoff <[EMAIL PROTECTED]> wrote: > You can try.. > > select substring(emailCol,2,LEN(emailCol)) from tablename > > Hope it helps! > > -Brian- > > > > > -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 24, 2006 2:05 PM > To: CF-Talk > Subject: db query > > Hii. I have a number of email addresses in my db that start with a > single quote. Like this: '[EMAIL PROTECTED] > '[EMAIL PROTECTED] > > etc. > > I need to run a query to strip off all the ' single quotes out of the > email addresses. I am using MS SQL 2000. > > Please help. > > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241331 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: db query
You can try.. select substring(emailCol,2,LEN(emailCol)) from tablename Hope it helps! -Brian- -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 2:05 PM To: CF-Talk Subject: db query Hii. I have a number of email addresses in my db that start with a single quote. Like this: '[EMAIL PROTECTED] '[EMAIL PROTECTED] etc. I need to run a query to strip off all the ' single quotes out of the email addresses. I am using MS SQL 2000. Please help. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241330 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: db query problem
It's confusing to see what you're trying to acheive because you can't tell from the query code you posted which fields refer to which table. It would help if you assigned table aliases, e.g. SELECT a.field, b.field FROM tableA a, tableB b and posted the code again. Beyond that, maybe UNION would do the trick? SELECT mls, type, asking_price, address, city, state, zip FROM Commercial WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' UNION SELECT mls, type, asking_price, address, city, state, zip FROM Multi_Family WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' UNION SELECT mls, type, asking_price, address, city, state, zip FROM Land WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' UNION cobbles all SELECT statements together, as long as each has the same fields. Check documentation depending on your DB. HTH, Gyrus [EMAIL PROTECTED] work: http://www.tengai.co.uk play: http://www.norlonto.net PGP key available - Original Message - From: "Chris Edwards" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, October 28, 2002 6:08 PM Subject: db query problem > Hi > > I need to condense these queries into one, can anyone help? > > > SELECT mls, type, asking_price, address, city, state, zip > FROM Residential, Commercial, Multi_Family, Land > WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' > > > > SELECT mls, type, asking_price, address, city, state, zip > FROM Commercial > WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' > > > > SELECT mls, type, asking_price, address, city, state, zip > FROM Multi_Family > WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' > > > > SELECT mls, type, asking_price, address, city, state, zip > FROM Land > WHERE listing_office #Request.App.Idx# AND agent = '#Request.User.Idx#' > > > -- > Chris Edwards > Web Application Developer > Outer Banks Internet, Inc. > 252-441-6698 > [EMAIL PROTECTED] > http://www.OuterBanksInternet.com > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq 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
RE: Db query question
I got it. My problem is, well here goes: a) - ColdFusion 5 (buy the Enterprise version for the smarts that Macromedia fooled us about, eg: app archiving) b) = NT Workstation 4 c) - 64Mb RAM d) - 130Mb Access db P.S. - God I love caching! P.S.S. - "Beer" is right at the top of the database as opposed to, say, WINE, or LAMB CUTLETS WITH BRINE. P.S.S.S. - ColdFusion 5 is not the problem, although I've given up on downloading CFStudio5B2. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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: Db query question
Hi Peter Something like this should do the trick select f.LONG_NAME, n.VALUE, n.NUTRIENT from NIP_FOOD f, NIP_Nutrient n where f.id = '01A10014' Then just output the query as normal (without the aliases) HTH Mark Mark Smyth Macromedia Certified ColdFusion Developer Systems Union eBusiness Solutions 01865 880800 [EMAIL PROTECTED] www.systemsunion.net -Original Message- From: Tilbrook, Peter [mailto:[EMAIL PROTECTED]] Sent: 20 August 2001 05:35 To: CF-Talk Subject: Db query question I've been given a massive (130Mb) Access database with two tables. NIP_FOOD and NIP_Nutrient. NIP_FOOD contains a unique ID field which is Alphanumeric (eg: 01A10014) and NOT indexed, eg: ID LONG_NAME 01A10014Beer, Ale 01A10015Beer, Bitter Or Draught 01A10016Beer, Regular Alcohol, Home Brewed 01A10017Beer, Lager NIP_Nutrient contains a field called "FOOD_ID" which is seven seperate rows of the same value as ID (but not linked). eg: FOOD_ID VALUE NUTRIENT 01A10014191.574 ENERGY-SOA 01A100140.4 PROT 01A100140 FAT 01A100140 TOTSATFD 01A100142.492 CHO-SOA 01A100140 TOTSUG 01A1001410NA-SOA What would be the way to retrieve all of the fields from both tables using the ID value (eg: 01A10014) but have it output a single row, eg: ID LONG_NAME ENERGY-SOAPROT FATTOTSATFD CHO-SOATOTSUG NA-SOA 01A10014 Beer, Ale 191 0.4 0 0 2.492 010 01A10015 Beer, Bitter 191.622 0.3 0 0 2.726 0.2 7 etc Thanks! Peter Tilbrook ColdFusion Applications Developer Australia New Zealand Food Authority Boeing House 55 Blackall Street BARTON ACT 2600 Ph: +61-2-6271 2256 Fax: +61-2-6271 2278 http://www.anzfa.gov.au ** This transmission is intended only for the use of the addressee(s) and may contain confidential or legally privileged information. If you are not the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you have received this transmission in error, please notify the ANZFA IT helpdesk prior to deleting all copies of this transmission together with any attachments. ANZFA helpdesk: E-mail: [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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: DB Query
so you want a list of proucts and both manufacturers? try a double join. (please note. this assumes that there are always values for both ManID and RealManID. if they are not always populated, make the joins OUTER and you'll be good.) SELECT p.pid, p., m1.manname, m2.manname FROM products p, manufacturers m1, manufacturers m2 WHERE p.manid = m1.manid AND p.realmanid = m2.manid chris olive, cio cresco technologies [EMAIL PROTECTED] http://www.crescotech.com -Original Message- From: Duane Boudreau [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 31, 2001 10:24 AM To: CF-Talk Subject: DB Query I have a couple of tables I am working with that do not have very good PK/FKs setup. (Unfortunately this is not my schema and I don't have the luxury of changing it) 1) Manufactures [ManID, ManName, ] 2) Products [PID, ManID, RealManID ] ManID and RealManID map to MID and usually contain differing values. I need (hopefully) in one query to extract both the Real Manufacturer Name and the other Manufacturer Name. Duane ~~ 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
