Re: can i do this with sql?
i actually need the id's with no row returned as if they were there (but with default values)... they do not need to be inserted into the table, just returned as if they were in there. j - Original Message - From: "sherzodR" <[EMAIL PROTECTED]> To: "Jamie Burns" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, November 27, 2001 1:24 PM Subject: Re: can i do this with sql? > > > i belive you want..: > > SELECT * FROM your_table WHERE id < 9 ORDER BY id LIMIT 9; > > But when it comes to generating missing items... I'm not sure if > i got you right though, but they should exist. MySQL cannot > make the rows that do not exist > > > > Jamie Burns wrote: > > : Hi all, > : > : I have a table like this: > : > : iddatayear > : > : 1 201 > : 2 201 > : 6 201 > : 7 201 > : 8 201 > : 11201 > : > : What i want to be able to get is these results from an sql query (given that > : i want id's 1-9): > : > : iddatayear > : > : 1 201 > : 2 201 > : 3 00 > : 4 00 > : 5 00 > : 6 201 > : 7 201 > : 8 201 > : 9 00 > : > : Basically it has made up the rows with id's 3,4, 5 and 9 because they didnt > : exist in the table. > : > : Can anybody give me any clues how to achieve this? > : > : Thanks, > : > : Jamie Burns. > : > : > : - > : Before posting, please check: > :http://www.mysql.com/manual.php (the manual) > :http://lists.mysql.com/ (the list archive) > : > : To request this thread, e-mail <[EMAIL PROTECTED]> > : To unsubscribe, e-mail <[EMAIL PROTECTED]> > : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > : > > -- > sherzodR <[EMAIL PROTECTED]> > use CGI::Session; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
can i do this with sql?
Hi all, I have a table like this: iddatayear 1 201 2 201 6 201 7 201 8 201 11201 What i want to be able to get is these results from an sql query (given that i want id's 1-9): iddatayear 1 201 2 201 3 00 4 00 5 00 6 201 7 201 8 201 9 00 Basically it has made up the rows with id's 3,4, 5 and 9 because they didnt exist in the table. Can anybody give me any clues how to achieve this? Thanks, Jamie Burns. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
Ok, i have figured it out... SELECT COUNT(DISTINCT name) from tester WHERE team = 'support' But this doesnt work on my development box which runs an older version of mysql! Does anybody know how to get this type of functionality without the DISTINCT keyword inside count? :o) Jamie. - Original Message - From: "Lezz Giles" <[EMAIL PROTECTED]> To: "Jamie Burns" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, September 14, 2001 2:29 PM Subject: Re: surely an easy quick one > I haven't tested this; I'm not an SQL guru. I just > like a challenge :-) > > SELECT COUNT(*) FROM table WHERE team = "support" GROUP BY name; > > Lezz Giles > > - Original Message - > From: "Jamie Burns" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, September 14, 2001 9:08 AM > Subject: surely an easy quick one > > > > Hello again :o) > > > > This is surely an easy quick one but my brain wont work! > > > > I have a table (ref, name, team, seminar_ref) like: > > > > 1billsupport100 > > 2billsupport101 > > 3billsupport102 > > 4ben development 201 > > 5ben development 103 > > 6ben development 204 > > 7bob support 104 > > 8bob support102 > > 9bob support107 > > 7pat support 102 > > 8pat support 202 > > 9pat support 105 > > > > I need to get a count of how many individual people are in the table, that > > belong to a certain group. So if I wanted to find all individual people in > > support the answer in this case it would be 3 (bill, bob and pat). I dont > > need their names, just the count. > > > > Thanks, > > > > Jamie. > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
this doesnt work as i need either :o( it returns 3 rows, each with a count of 3 inside. i just want to get a *single row* with the number 3 in it (for my example). :o( thanks for trying though... - Original Message - From: "Lezz Giles" <[EMAIL PROTECTED]> To: "Jamie Burns" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, September 14, 2001 2:29 PM Subject: Re: surely an easy quick one > I haven't tested this; I'm not an SQL guru. I just > like a challenge :-) > > SELECT COUNT(*) FROM table WHERE team = "support" GROUP BY name; > > Lezz Giles > > - Original Message - > From: "Jamie Burns" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, September 14, 2001 9:08 AM > Subject: surely an easy quick one > > > > Hello again :o) > > > > This is surely an easy quick one but my brain wont work! > > > > I have a table (ref, name, team, seminar_ref) like: > > > > 1billsupport100 > > 2billsupport101 > > 3billsupport102 > > 4ben development 201 > > 5ben development 103 > > 6ben development 204 > > 7bob support 104 > > 8bob support102 > > 9bob support107 > > 7pat support 102 > > 8pat support 202 > > 9pat support 105 > > > > I need to get a count of how many individual people are in the table, that > > belong to a certain group. So if I wanted to find all individual people in > > support the answer in this case it would be 3 (bill, bob and pat). I dont > > need their names, just the count. > > > > Thanks, > > > > Jamie. > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: surely an easy quick one
this doesnt work as i need :o( it returns a count of 9, not 3 in my example. i want to know how many *individual* people are in the database for a given 'team'. in my example there are three in 'support' (this is what i want to know), and they are each in the table three times (this is where your query gets 9 from). :o( - Original Message - From: "Stefan Pinkert" <[EMAIL PROTECTED]> To: "Jamie Burns" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, September 14, 2001 2:25 PM Subject: Re: surely an easy quick one > select count(*) from table where team='support' group by team > > -Ursprüngliche Nachricht- > Von: Jamie Burns [mailto:[EMAIL PROTECTED]] > Gesendet: Freitag, 14. September 2001 15:09 > An: [EMAIL PROTECTED] > Betreff: surely an easy quick one > > > Hello again :o) > > This is surely an easy quick one but my brain wont work! > > I have a table (ref, name, team, seminar_ref) like: > > 1billsupport100 > 2billsupport101 > 3billsupport102 > 4ben development 201 > 5ben development 103 > 6ben development 204 > 7bob support 104 > 8bob support102 > 9bob support107 > 7pat support 102 > 8pat support 202 > 9pat support 105 > > I need to get a count of how many individual people are in the table, > that > belong to a certain group. So if I wanted to find all individual people > in > support the answer in this case it would be 3 (bill, bob and pat). I > dont > need their names, just the count. > > Thanks, > > Jamie. > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
surely an easy quick one
Hello again :o) This is surely an easy quick one but my brain wont work! I have a table (ref, name, team, seminar_ref) like: 1billsupport100 2billsupport101 3billsupport102 4ben development 201 5ben development 103 6ben development 204 7bob support 104 8bob support102 9bob support107 7pat support 102 8pat support 202 9pat support 105 I need to get a count of how many individual people are in the table, that belong to a certain group. So if I wanted to find all individual people in support the answer in this case it would be 3 (bill, bob and pat). I dont need their names, just the count. Thanks, Jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Even Possible in MySQL?
Hello, I have a query as follows: SELECT products.*, avg(ratings.rating) AS rating FROM products, ratings WHERE products.ref = ratings.product_ref GROUP BY products.ref ORDER BY rating In one sense this query works fine - it adds a new column called 'rating' (which is a dynamically calculated average of ratings given to a certain product) to my product results. My problem is that it only works if a rating has been given for a product. When a new product is added, it will have no ratings, and so it will not be returned in any of my queries. Is there any way to assign a default value to the 'rating' column if actual row(s) do not exist in the ratings table? I dont really want to have to make a dummy rating row just to trick it into working. Am i making sense? I hope so ;o) Jamie Burns. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: quickie!
that is the answer, but i think i was looking for something different. because i am using php, i am used to results being returned in an array, and was hoping to get this sort of thing out... $results[0]['id']// table1.id $results[0]['name']// table1.name $results[0]['subject'][0] // table2.subject $results[0]['subject'][1]// table2.subject $results[0]['subject'][2] // table2.subject i am guessing mysql doesnt have a notion of multidimensional results... i will sort them myself ;o) thanks anyway, just me trying to make things too efficient... jamie. - Original Message - From: "Dennis Salguero" <[EMAIL PROTECTED]> To: "Jamie Burns" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, August 05, 2001 10:32 PM Subject: Re: quickie! > Are you looking for: > > SELECT table1.name, table2.subject > FROM table1, table2 > WHERE table1.id=table2.student_id > > Good Luck, > > Dennis > ****** > Beridney Computer Services > http://www.beridney.com > > - Original Message - > From: "Jamie Burns" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Sunday, August 05, 2001 2:25 PM > Subject: quickie! > > > hi.. > > can someone tell me how i get the data from two tables, where table1 will > have one row, and table2 will have many rows? > > eg: > > > > table1: > > idname > > 1barney > 2fred > 3wilma > > table2: > > idstudent_id subject > > 11 english > 11 cookery > 11 maths > 11 science > 12 woodwork > 13 cookery > > > > do i have to use two queries to get all this data out in usable form? > > thanks, > > jamie. > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
quickie!
hi.. can someone tell me how i get the data from two tables, where table1 will have one row, and table2 will have many rows? eg: table1: idname 1barney 2fred 3wilma table2: idstudent_id subject 11 english 11 cookery 11 maths 11 science 12 woodwork 13 cookery do i have to use two queries to get all this data out in usable form? thanks, jamie.
help!
hi... i have a table as follows: refsmallint(5) product_refsmallint(5) ratingsmallint(1) is there any way i can get the average rating for each product_ref? for example... refp_refrating 114 215 313 415 524 625 733 832 so that i could find out that: product_ref 1, has an average rating of 4.25 product_ref 2, has an average rating of 4.5 product_ref 3, has an average rating of 2.5 and could i possibly return the 10 highest rows? any help with this would be really appreciated... jamie burns.
portable query?
hi all... i have a mysql query as follows: SELECT *, ( if((description LIKE '%keyword_one%'), 1, 0)+ if((description LIKE '%keyword_two%'), 1, 0)+ if((description LIKE '%keyword_three%'), 1, 0)+ if((description LIKE '%keyword_four%'), 1, 0) ) as keyword_score, FROM table WHERE ( (description LIKE '%keyword_one%) OR (description LIKE '%keyword_two%) OR (description LIKE '%keyword_three%) OR (description LIKE '%keyword_four%) ) GROUP BY ref ORDER BY keyword_score DESC; this query is great for my search engine because it ranks the results based on how many keywords were found in each row (users love this). i know have to think about making it portable (i found the if() statement didnt work in PostgreSQL). are there any portable equivilents to this MySQL specific query? thanks for your thoughts, jamie burns. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
