RE: Possible in SQL?
> I have a table that looks something like this. > > Com1 genderfname lname > 001 M Matt Smith > 001 M Bill Wright > 005 FKara Brown > 005 M Mike White > > > > What I need to do is pull a record from each "com1" group > that meets my where criteria. Say from above I want all the > males. I would want the answer to be > > 001 M Matt Smith > 005 M Mike White > > Or > > 001 M Bill Wright > 005 M Mike White > > I don't care which record I just want ONE per com1 id meeting > the criteria. > I tried using distinct but that will not work as you have to > list all the field names out and then group but the none > distinct ones which gives me all the com1's meeting the where > criteria. Any idea's? You're not looking for distinct records, you want one random record from within a set of records that otherwise match your search conditions. You can fix this within your SQL using "TOP 1" or whatever similar functionality your database provides to get the first matching record within a set, or you can fix this within your CFML by using the GROUP attribute of CFOUTPUT to just show the first row within the set. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241145 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: Possible in SQL?
I tried that. That will give you records with com1 showing up more than once since the names ect are not distinct sql returns all the combinations. If it would only give me one per change in com1 I would be done. Jeremy -Original Message- From: Snake [mailto:[EMAIL PROTECTED] Sent: Monday, May 22, 2006 8:52 AM To: CF-Talk Subject: RE: Possible in SQL? I would have thought distinct would do what you want, are you sure you use dit correctly. Select distinct com1, gender, fname, lname FROM mytable WHERE gender = 'M' Group by com1, gender, fname, lname - Snake -Original Message- From: Jeremy Bunton [mailto:[EMAIL PROTECTED] Sent: 22 May 2006 13:34 To: CF-Talk Subject: Possible in SQL? Hello, I have a table that looks something like this. Com1 genderfname lname 001 M Matt Smith 001 M Bill Wright 005 FKara Brown 005 M Mike White What I need to do is pull a record from each "com1" group that meets my where criteria. Say from above I want all the males. I would want the answer to be 001 M Matt Smith 005 M Mike White Or 001 M Bill Wright 005 M Mike White I don't care which record I just want ONE per com1 id meeting the criteria. I tried using distinct but that will not work as you have to list all the field names out and then group but the none distinct ones which gives me all the com1's meeting the where criteria. Any idea's? Jeremy ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241141 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: Possible in SQL?
I would have thought distinct would do what you want, are you sure you use dit correctly. Select distinct com1, gender, fname, lname FROM mytable WHERE gender = 'M' Group by com1, gender, fname, lname - Snake -Original Message- From: Jeremy Bunton [mailto:[EMAIL PROTECTED] Sent: 22 May 2006 13:34 To: CF-Talk Subject: Possible in SQL? Hello, I have a table that looks something like this. Com1 genderfname lname 001 M Matt Smith 001 M Bill Wright 005 FKara Brown 005 M Mike White What I need to do is pull a record from each "com1" group that meets my where criteria. Say from above I want all the males. I would want the answer to be 001 M Matt Smith 005 M Mike White Or 001 M Bill Wright 005 M Mike White I don't care which record I just want ONE per com1 id meeting the criteria. I tried using distinct but that will not work as you have to list all the field names out and then group but the none distinct ones which gives me all the com1's meeting the where criteria. Any idea's? Jeremy ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241123 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: Possible in SQL?
fixing my query...I had used used foo as the tablename originally, and not changed it in both places... select * from sometable a WHERE a.gender = 'M' AND lname = (SELECT max(lname) FROM sometable WHERE com1=a.com1) -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241122 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: Possible in SQL?
You could do it with a subquery select * from sometable a WHERE a.gender = 'M' AND lname = (SELECT max(lname) FROM foo WHERE com1=a.com1) (of course, this assumes that lname is unique within a com1, which may not be the case...you may need to add on a identity field to make sure you only get one value for each com1) -- Jim Wright Wright Business Solutions [EMAIL PROTECTED] 919-417-2257 ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241121 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
Possible in SQL?
Hello, I have a table that looks something like this. Com1 genderfname lname 001 M Matt Smith 001 M Bill Wright 005 FKara Brown 005 M Mike White What I need to do is pull a record from each "com1" group that meets my where criteria. Say from above I want all the males. I would want the answer to be 001 M Matt Smith 005 M Mike White Or 001 M Bill Wright 005 M Mike White I don't care which record I just want ONE per com1 id meeting the criteria. I tried using distinct but that will not work as you have to list all the field names out and then group but the none distinct ones which gives me all the com1's meeting the where criteria. Any idea's? Jeremy ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:241120 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: Is this possible in SQL
Join the tables together so they are treated like a single table...(assuming they are join-able) SELECT c.categoryid, sc.sub_CategoryId FROM categories c, sub_category sc WHERE c.categoryID = sc.categoryID AND (c.category = '#form.searchVal#' OR sc.sub_Category = '#form.searchVal#') LIMIT 1 +---+ Bryan Love Database Analyst Macromedia Certified Professional Internet Application Developer TeleCommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis "Let's Roll" - Todd Beamer, Flight 93 -Original Message- From: Andy J [mailto:[EMAIL PROTECTED] Sent: Monday, August 09, 2004 4:22 PM To: CF-Talk Subject: Is this possible in SQL I have two tables which I want to query at the same time. I'm thinking something along the lines of SELECT c.categoryid, sc.sub_CategoryId FROM categories c, sub_category sc WHERE c.category = '#form.searchVal#' OR sc.sub_Category = '#form.searchVal#' LIMIT 1 But where this is a left join it will always return a result for either column whether it makes a match or not. I could do something like the code below, but it seems to cumbersum for the job. SELECT c.categoryid AS catId FROM categories c WHERE c.category = '#form.searchVal#' SELECT sc.sub_CategoryId AS catId FROM sub_category sc WHERE sc.sub_Category = '#form.searchVal#' Cheers Andy --- Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Is this possible in SQL
Cheers Matt, Worked a charm >I have two tables which I want to query at the same time. I'm thinking >something along the lines of > >SELECT c.categoryid, sc.sub_CategoryId >FROM categories c, sub_category sc >WHERE c.category = '#form.searchVal#' OR sc.sub_Category = >'#form.searchVal#' >LIMIT 1 > >But where this is a left join it will always return a result for either >column whether it makes a match or not. > >I could do something like the code below, but it seems to cumbersum for the >job. > >SELECT c.categoryid AS catId >FROM categories c >WHERE c.category = '#form.searchVal#' > > > > > SELECT sc.sub_CategoryId AS catId > FROM sub_category sc > WHERE sc.sub_Category = '#form.searchVal#' > > > > > > >Cheers > >Andy > >--- > >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Is this possible in SQL
Have you tried union? SELECT. UNION SELECT. _ From: Andy J [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 11:22 a.m. To: CF-Talk Subject: Is this possible in SQL I have two tables which I want to query at the same time. I'm thinking something along the lines of SELECT c.categoryid, sc.sub_CategoryId FROM categories c, sub_category sc WHERE c.category = '#form.searchVal#' OR sc.sub_Category = '#form.searchVal#' LIMIT 1 But where this is a left join it will always return a result for either column whether it makes a match or not. I could do something like the code below, but it seems to cumbersum for the job. SELECT c.categoryid AS catId FROM categories c WHERE c.category = '#form.searchVal#' SELECT sc.sub_CategoryId AS catId FROM sub_category sc WHERE sc.sub_Category = '#form.searchVal#' Cheers Andy --- Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Is this possible in SQL
I have two tables which I want to query at the same time. I'm thinking something along the lines of SELECT c.categoryid, sc.sub_CategoryId FROM categories c, sub_category sc WHERE c.category = '#form.searchVal#' OR sc.sub_Category = '#form.searchVal#' LIMIT 1 But where this is a left join it will always return a result for either column whether it makes a match or not. I could do something like the code below, but it seems to cumbersum for the job. SELECT c.categoryid AS catId FROM categories c WHERE c.category = '#form.searchVal#' SELECT sc.sub_CategoryId AS catId FROM sub_category sc WHERE sc.sub_Category = '#form.searchVal#' Cheers Andy --- Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
