Re: Query Question
I wrote: Maybe something like this? SELECT Data1 FROM table_name WHERE Data3 = 1 GROUP BY Data1 HAVING SUM(Data2 = 141) = 0; Actually that doesn't make sense. I was thinking of the case where there's a left join involved. In your case, it should just be this: SELECT DISTINCT Data1 FROM table_name WHERE Data3 = 1 AND Data2 141; [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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: Query Question
That would filter out the records containing 141 before it did the group by That would mean I could get a group that had 141 in it 4 3 141 1 9 3 142 1 103 143 1 So, here it would not event look at record 4, then it would return me a group that contains a Data1 value of 3, but I do not require that result because it's original group contains 141 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 11:58 AM To: Luc Foisy Cc: MYSQL-List (E-mail) Subject: Re: Query Question :ID Data1 Data2 Data3 :1 1 141 1 :2 5 140 1 :For my return I only want a single instance of Data1, so it will most likely need be GROUP BY Data1 : :I would like those records to include the group that does not contain a Data2 value of 141 : :There is also a WHERE clause on Data3 = 1 : :The return would include these values for Data1 : :7 :5 :2 :4 : Try this, if you haven't already SELECT ID, Data1 FROM sm_table WHERE Data1 != 141 GROUP by Data1; - 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: Query Question
Because I don't know what Data1 is, thats the result I want to find -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 12:10 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: RE: Query Question Why cant you filter on Data1? I meant select * from xxx where data2 141 and data3 = 1 and (data1 = 7 or data1 = 5 or data1 = 2 or data1 = 4) groupby data1 -Original Message- From: Luc Foisy [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 10:51 AM To: MYSQL-List (E-mail) Subject: RE: Query Question I suppose I should have specified that I would not be able to filter by Data1 -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 11:52 AM To: Luc Foisy Subject: RE: Query Question select * from xxx where data2 141 and data3 = 1 and (data3 = 7 or data3 = 5 or data3 = 2 or data3 = 4) groupby data1 -Original Message- From: Luc Foisy [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 10:32 AM To: MYSQL-List (E-mail) Subject: Query Question Some ficticious data ID Data1 Data2 Data3 1 1 141 1 2 5 140 1 3 4 142 1 4 3 141 1 5 2 142 1 6 5 142 1 7 7 140 1 8 2 143 1 9 3 142 1 10 3 143 1 11 4 144 1 12 2 144 1 13 8 141 2 14 9 140 2 15 6 142 2 16 11 141 2 17 0 142 2 18 9 142 2 19 10 140 2 20 0 143 2 21 11 142 2 22 3 143 2 23 6 144 2 24 0 144 2 For my return I only want a single instance of Data1, so it will most likely need be GROUP BY Data1 I would like those records to include the group that does not contain a Data2 value of 141 There is also a WHERE clause on Data3 = 1 The return would include these values for Data1 7 5 2 4 Is there some way to do this with a single query? If this is unclear, let me know Luc Foisy Technical Magic - www.technical-magic.com 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 721-8850 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - 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 - 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: Query Question
Hi, You can try something like this : select (CASE WHEN Data2141 THEN Data1 END) from your_table where Data3=1 group by Data1; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Luc Foisy [EMAIL PROTECTED] To: MYSQL-List (E-mail) [EMAIL PROTECTED] Sent: Monday, August 19, 2002 6:32 PM Subject: Query Question Some ficticious data ID Data1 Data2 Data3 1 1 141 1 2 5 140 1 3 4 142 1 4 3 141 1 5 2 142 1 6 5 142 1 7 7 140 1 8 2 143 1 9 3 142 1 10 3 143 1 11 4 144 1 12 2 144 1 13 8 141 2 14 9 140 2 15 6 142 2 16 11 141 2 17 0 142 2 18 9 142 2 19 10 140 2 20 0 143 2 21 11 142 2 22 3 143 2 23 6 144 2 24 0 144 2 For my return I only want a single instance of Data1, so it will most likely need be GROUP BY Data1 I would like those records to include the group that does not contain a Data2 value of 141 There is also a WHERE clause on Data3 = 1 The return would include these values for Data1 7 5 2 4 Is there some way to do this with a single query? If this is unclear, let me know Luc Foisy Technical Magic - www.technical-magic.com 1 Stafford Road, Suite 325, Nepean, Ontario, K2H 1B9 Phone: (613) 721-8850 Fax: (613) 596-5096 E-Mail: [EMAIL PROTECTED] Fulfilling the Promise of Technology - 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: Query Question
I wrote: Actually that doesn't make sense. I was thinking of the case where there's a left join involved. In your case, it should just be this: SELECT DISTINCT Data1 FROM table_name WHERE Data3 = 1 AND Data2 141; Ignore that correction. It seems that what you want is indeed this: SELECT Data1 FROM table_name WHERE Data3 = 1 GROUP BY Data1 HAVING SUM(Data2 = 141) = 0; [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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: Query Question
Yes that's it, Thanks! Ignore that correction. It seems that what you want is indeed this: SELECT Data1 FROM table_name WHERE Data3 = 1 GROUP BY Data1 HAVING SUM(Data2 = 141) = 0; [Filter fodder: SQL] Luc - 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: PHP/MySQL Search Engine Query Question
Pada Sat, 27 Jul 2002 21:32:48 -0500 Paul Maine [EMAIL PROTECTED] menulis : $string =1972 Ford Mustang Using the following SQL statement: SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search% I want to return all records that have Mustang AND 1972 AND Ford. my suggestion : change your script into this one : select * from whatevertable where $string; $string=whatevercolumns like \%1972%\ and whatevercolumns like \%Ford%\ and whatevercolumns like \%1972%\ or you can try this : $query=1972 Ford Mustang $query=ereg_replace( ,%\ and whatevercolumns like \%,$query); $string=whatevercolumns like \%$query%\; select * from whatevertable where $string; -- Write clearly - don't be too clever. - The Elements of Programming Style (Kernighan Plaugher) MySQL 3.23.51 : up 37 days, Queries : 353.647 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan (12790) Phone : +62 21 79199577 - Web : http://1rstwap.com - 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: PHP/MySQL Search Engine Query Question
This is more of a php question. explode $string on (space) which will give you an array. Loop through the array first with $sql.=WHERE whatevercolumn LIKE '%$val%' then for every other entry change WHERE to AND. Serge. - Original Message - From: Paul Maine [EMAIL PROTECTED] To: MySQL MySQL [EMAIL PROTECTED] Sent: Saturday, July 27, 2002 10:32 PM Subject: PHP/MySQL Search Engine Query Question I am currently working on a website that is implemented using PHP and MySQL. The site currently has a simple search engine that allows a shopper to type in a search string that is stored in $search. For example, if a shopper types in 1972 Ford Mustang $string =1972 Ford Mustang Using the following SQL statement: SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search% Records are returned that have this exact string and in this exact order (I'm aware a wild card character is included on the front and back of the string). My desire is to be able to logically AND each token of the search together independent or the order of the tokens. I want to return all records that have Mustang AND 1972 AND Ford. Since a shopper inputs the search string in advance I don't know how many tokens will be used. I would appreciate any suggestions. Regards, Paul - 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
PHP/MySQL Search Engine Query Question
I am currently working on a website that is implemented using PHP and MySQL. The site currently has a simple search engine that allows a shopper to type in a search string that is stored in $search. For example, if a shopper types in 1972 Ford Mustang $string =1972 Ford Mustang Using the following SQL statement: SELECT * FROM whatevertable WHERE whatevercolumn LIKE '%$search% Records are returned that have this exact string and in this exact order (I'm aware a wild card character is included on the front and back of the string). My desire is to be able to logically AND each token of the search together independent or the order of the tokens. I want to return all records that have Mustang AND 1972 AND Ford. Since a shopper inputs the search string in advance I don't know how many tokens will be used. I would appreciate any suggestions. Regards, Paul - 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 question
Hello all, I am trying to build a relatively complex query to get the average and standard deviation of various testing variables which ultimately will be filtered/grouped by age and other parameters. Item #1 shows the original query and output: select count(Test1000.OID) as N, avg(Accuracy) as AccAvg, std(Accuracy) as AccStd, avg(AvgRespT) as RTAvg, std(AvgRespT) as RTStd , from Test1000,ROrders,ptProt,protocols,RPtDemog WHERE ROrders.OID=Test1000.OID AND ptProt.protID=protocols.protID AND ptProt.PID=ROrders.PID AND ptProt.PID=RPtDemog.PID AND Language=1 AND Trusted='Yes' - N AccAvg AccStd RTAvg RTStd 402 92.9680 9.7709 445.2773121.2830 Item #2 is the same query but has an added Age Bracket field to be grouped by: [if its hard to read, age is computed as the (date the person was enrolled in a clinical trial - their DOB)/365 and this Age is separated into brackets - This part works fine...] select count(Test1000.OID) as N, if (round((to_days(dateStart)-to_days(DOB))/365)30,'30', if (round((to_days(dateStart)-to_days(DOB))/365)=30 AND round((to_days(dateStart)-to_days(DOB))/365)50,'30-50', if (round((to_days(dateStart)-to_days(DOB))/365)=50 AND round((to_days(dateStart)-to_days(DOB))/365)70,'50-70','70+'))) as Bracket, avg(Accuracy) as AccAvg, std(Accuracy) as AccStd, avg(AvgRespT) as RTAvg, std(AvgRespT) as RTStd from Test1000,ROrders,ptProt,protocols,RPtDemog WHERE ROrders.OID=Test1000.OID AND ptProt.protID=protocols.protID AND ptProt.PID=ROrders.PID AND ptProt.PID=RPtDemog.PID AND Language=1 AND Trusted='Yes' Group By Bracket --- N Bracket AccAvg AccStd RTAvg RTStd 121 30-50 96.2500 0. 406.36670. 59 50-70 94.2542 0. 435.76270. 129 70+ 88.0833 0. 533.52780. 93 30 93.6250 0. 396.40910. MY QUESTION:What happened to the standard deviation columns between the first and second query?I would appreciate any insights anyone could offer!Thanks in advance.Judy SimonWebmaster DB Systems EngineerNeuroTrax Israel LTD, Shilat, Israel Tel: +972-8-976-3067 Fax: +972-8-976-3068 - 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 question
Hello all, I am trying to build a relatively complex query to get the average and standard deviation of various testing variables which ultimately will be filtered/grouped by age and other parameters. Item #1 shows the original query and output: select count(Test1000.OID) as N, avg(Accuracy) as AccAvg, std(Accuracy) as AccStd, avg(AvgRespT) as RTAvg, std(AvgRespT) as RTStd , from Test1000,ROrders,ptProt,protocols,RPtDemog WHERE ROrders.OID=Test1000.OID AND ptProt.protID=protocols.protID AND ptProt.PID=ROrders.PID AND ptProt.PID=RPtDemog.PID AND Language=1 AND Trusted='Yes' - N AccAvg AccStd RTAvg RTStd 402 92.9680 9.7709 445.2773121.2830 Item #2 is the same query but has an added Age Bracket field to be grouped by: [if its hard to read, age is computed as the (date the person was enrolled in a clinical trial - their DOB)/365 and this Age is separated into brackets - This part works fine...] select count(Test1000.OID) as N, if (round((to_days(dateStart)-to_days(DOB))/365)30,'30', if (round((to_days(dateStart)-to_days(DOB))/365)=30 AND round((to_days(dateStart)-to_days(DOB))/365)50,'30-50', if (round((to_days(dateStart)-to_days(DOB))/365)=50 AND round((to_days(dateStart)-to_days(DOB))/365)70,'50-70','70+'))) as Bracket, avg(Accuracy) as AccAvg, std(Accuracy) as AccStd, avg(AvgRespT) as RTAvg, std(AvgRespT) as RTStd from Test1000,ROrders,ptProt,protocols,RPtDemog WHERE ROrders.OID=Test1000.OID AND ptProt.protID=protocols.protID AND ptProt.PID=ROrders.PID AND ptProt.PID=RPtDemog.PID AND Language=1 AND Trusted='Yes' Group By Bracket --- N Bracket AccAvg AccStd RTAvg RTStd 12130-5096.2500 0. 406.3667 0. 5950-7094.2542 0. 435.7627 0. 12970+ 88.0833 0. 533.5278 0. 93 30 93.6250 0. 396.4091 0. MY QUESTION:What happened to the standard deviation columns between the first and second query? I would appreciate any insights anyone could offer! Thanks in advance. Judy Simon Webmaster DB Systems Engineer NeuroTrax Israel LTD, Shilat, Israel Tel: +972-8-976-3067 Fax: +972-8-976-3068 - 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: SQL design/query question
Well maybe I sould map this out more clearly... members +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | rowid | int(11) | | PRI | NULL| auto_increment | | first_name| varchar(20) | YES | | NULL|| | last_name | varchar(20) | YES | | NULL|| | address1 | varchar(60) | YES | | NULL|| | address2 | varchar(60) | YES | | NULL|| | city | varchar(20) | YES | | NULL|| | state | varchar(20) | YES | | NULL|| | zip | varchar(12) | YES | | NULL|| | region_id | int(10) | YES | | NULL|| | subregion_id | int(11) | YES | | NULL|| +---+-+--+-+-++ regions +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | rowid | bigint(20) | | PRI | NULL| auto_increment | | name | varchar(20) | YES | | NULL|| +---+-+--+-+-++ subregions +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | rowid | bigint(20) | | PRI | NULL| auto_increment | | name | varchar(20) | YES | | NULL|| +---+-+--+-+-++ event +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | rowid| int(11) | | PRI | NULL| auto_increment | | event_type | smallint(6) | YES | | NULL|| | event_date | date| YES | | NULL|| | member_id| int(11) | YES | | NULL|| +--+-+--+-+-++ the results of the query should look like this: +--+-+++ --+ | region | subregion | first_name | last_name | Attended | +--+-+++ --+ I am interested in showing a list for a selected date so the date is not part of the output. I am currently using the following query to just show the list without the attendance information. SELECT regions.name as region, subregion.name as subregion, first_name, last_name FROM members, regions, subregion WHERE members.region_id=regions.rowid and members.subregion_id=subregion.rowid GROUP BY region, subregion; +--+-+++ | region | subregion | first_name | last_name | +--+-+++ The query you gave didn't seem to work right. It only gave me one line for every record in the event table. On Tuesday, July 2, 2002, at 08:45 PM, Cal Evans wrote: Right list. What you want is an outer join. Very simple example: person --- personID === name region === attendance --- attendanceID === date personID FK into person eventid FK into event === Select event.date, person.name, (attendance.personID is null) as present from attendance left outer join person on attendance.personID = person.personID order by person.region SHOULD give you a 0 in present if they were not there. (DISCLAIMER: It's late, I've not tested this code, and this is free advice, take it for what it's worth.) :) HTH, =C+ * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Chris Griffin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 02, 2002 9:08 PM To: [EMAIL PROTECTED] Subject: SQL design/query question If this is not the right list for this please direct me to the proper list. I have a table of name/address information. I need to keep track of a weekly event and if they participated or not. I thought I could have a table of dates and people record ids with just those that participated. The problem is I want to list all people with a check or not if they participated. I would like the list sorted by region (which is part of the people table) and by participation within that. Is this possible to do with the way I have set up the tables? If so what is the query. I can only
SQL design/query question
If this is not the right list for this please direct me to the proper list. I have a table of name/address information. I need to keep track of a weekly event and if they participated or not. I thought I could have a table of dates and people record ids with just those that participated. The problem is I want to list all people with a check or not if they participated. I would like the list sorted by region (which is part of the people table) and by participation within that. Is this possible to do with the way I have set up the tables? If so what is the query. I can only seem to create a query that only includes the ones that participate. If I can't do it this way is there another way that would keep the concept of a 'sparse matrix' in the participated table? I would like to do this so I don't have to have a record for every person for each week. Thanks. - 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: SQL design/query question
You need a LEFT JOIN. Read about it here: http://www.w3schools.com/sql/sql_join.asp Go down to the LEFT JOIN section. HTH, Jed I liked it when Chris Griffin wrote this to me: If this is not the right list for this please direct me to the proper list. I have a table of name/address information. I need to keep track of a weekly event and if they participated or not. I thought I could have a table of dates and people record ids with just those that participated. The problem is I want to list all people with a check or not if they participated. I would like the list sorted by region (which is part of the people table) and by participation within that. Is this possible to do with the way I have set up the tables? If so what is the query. I can only seem to create a query that only includes the ones that participate. If I can't do it this way is there another way that would keep the concept of a 'sparse matrix' in the participated table? I would like to do this so I don't have to have a record for every person for each week. Thanks. - 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: SQL design/query question
Right list. What you want is an outer join. Very simple example: person --- personID === name region === attendance --- attendanceID === date personID FK into person eventid FK into event === Select event.date, person.name, (attendance.personID is null) as present from attendance left outer join person on attendance.personID = person.personID order by person.region SHOULD give you a 0 in present if they were not there. (DISCLAIMER: It's late, I've not tested this code, and this is free advice, take it for what it's worth.) :) HTH, =C+ * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Chris Griffin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 02, 2002 9:08 PM To: [EMAIL PROTECTED] Subject: SQL design/query question If this is not the right list for this please direct me to the proper list. I have a table of name/address information. I need to keep track of a weekly event and if they participated or not. I thought I could have a table of dates and people record ids with just those that participated. The problem is I want to list all people with a check or not if they participated. I would like the list sorted by region (which is part of the people table) and by participation within that. Is this possible to do with the way I have set up the tables? If so what is the query. I can only seem to create a query that only includes the ones that participate. If I can't do it this way is there another way that would keep the concept of a 'sparse matrix' in the participated table? I would like to do this so I don't have to have a record for every person for each week. Thanks. - 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
Strings query question
I do not understand string comparison. Why doesn't the following work: SELECT id, songname FROM songs WHERE songname = Empire; All I get is: Empty set (0.00 sec) I know the string exists in the database: In record 135 exists: Empire | | 135 | Do I have to use LIKE? I want to match a string from one table with a string from another table so I can display the data from the matched record. select movietable.id, movietable.songname, songs.id, songs.songname FROM movietable, songs WHERE movietable.songname = songs.songname; Results: Empty set (0.19 sec) This doesn't work either select movietable.songname, movietable.ID, songs.songname, songs.id FROM movietable, songs WHERE movietable.songname = Empire; Yet, I know that movietable.songname contains a song called Empire and movietable.songs also contains a song called Empire How do I match the two strings? Running Linux with MySQL-3.23.32-1.7 - 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: Strings query question
Thanks, that worked for song named Empire, however, when I checked other song names such as songname called One I get two results: | id | songname | +++ | 35 | You're Still The One | | 57 | One | +++ 2 rows in set (0.00 sec) How do I get exact match only between two strings or remove whitespace before and after string? I can't seem to find any good emaples of string comparisons for exact match. Songname field is a VARCHAR(55). I thought VARCHAR saves only the length of the string where as CHAR adds whitespace to parts not used in the field Like is better because maybe there is a \n or \r\n after your string, further it seems there are many whitespaces after the word. Try WHERE songname LIKE %Empire%. just my 0,02 jan - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 23, 2002 2:27 PM Subject: Strings query question I do not understand string comparison. Why doesn't the following work: SELECT id, songname FROM songs WHERE songname = Empire; All I get is: Empty set (0.00 sec) I know the string exists in the database: In record 135 exists: Empire | | 135 | Do I have to use LIKE? I want to match a string from one table with a string from another table so I can display the data from the matched record. select movietable.id, movietable.songname, songs.id, songs.songname FROM movietable, songs WHERE movietable.songname = songs.songname; Results: Empty set (0.19 sec) This doesn't work either select movietable.songname, movietable.ID, songs.songname, songs.id FROM movietable, songs WHERE movietable.songname = Empire; Yet, I know that movietable.songname contains a song called Empire and movietable.songs also contains a song called Empire How do I match the two strings? Running Linux with MySQL-3.23.32-1.7 - 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: Strings query question
* [EMAIL PROTECTED] Thanks, that worked for song named Empire, however, when I checked other song names such as songname called One I get two results: | id | songname | +++ | 35 | You're Still The One | | 57 | One | +++ 2 rows in set (0.00 sec) How do I get exact match only between two strings or remove whitespace before and after string? To remove the unwanted \n or \r\n from your table, you can use the REPLACE() function: UPDATE songtable SET songname = TRIM(REPLACE(REPLACE(songname,'\n',''),'\r','')) Beware that this will remove _all_ return and linefeed characters, not only leading and trailing. The outer TRIM() function removes only leading/trailing _space_ characters. The outer TRIM() may be unnecessary if you don't have \n title in your data, only \ntitle, \rtitle or \r\ntitle. Removing '\r' is only needed if the data contains '\r', which it may do if the data comes from a windows environment. If the data is originally produced on your linux machine, you may simplify to: UPDATE songtable SET songname = REPLACE(songname,'\n','') I can't seem to find any good emaples of string comparisons for exact match. Exact matching is done using = (the equal sign), but you don't have an exact match in this case... URL: http://www.mysql.com/doc/S/t/String_functions.html URL: http://www.mysql.com/doc/S/t/String_comparison_functions.html Songname field is a VARCHAR(55). I thought VARCHAR saves only the length of the string where as CHAR adds whitespace to parts not used in the field CHAR fields are padded with _space_ characters, not any whitespace... and these space characters are automatically removed when you retrieve the data. Your \n and/or \r characters are considered a part of your string, and is not ignored. -- Roger - 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: Strings query question
SELECT id, songname FROM tablename WHERE TRIM(BOTH ' ' FROM songname)=One; See: http://www.mysql.com/doc/S/t/String_functions.html Gurhan - Original Message - From: [EMAIL PROTECTED] To: Jan Peuker [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, June 23, 2002 8:58 AM Subject: Re: Strings query question Thanks, that worked for song named Empire, however, when I checked other song names such as songname called One I get two results: | id | songname | +++ | 35 | You're Still The One | | 57 | One | +++ 2 rows in set (0.00 sec) How do I get exact match only between two strings or remove whitespace before and after string? I can't seem to find any good emaples of string comparisons for exact match. Songname field is a VARCHAR(55). I thought VARCHAR saves only the length of the string where as CHAR adds whitespace to parts not used in the field Like is better because maybe there is a \n or \r\n after your string, further it seems there are many whitespaces after the word. Try WHERE songname LIKE %Empire%. just my 0,02 jan - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 23, 2002 2:27 PM Subject: Strings query question I do not understand string comparison. Why doesn't the following work: SELECT id, songname FROM songs WHERE songname = Empire; All I get is: Empty set (0.00 sec) I know the string exists in the database: In record 135 exists: Empire | | 135 | Do I have to use LIKE? I want to match a string from one table with a string from another table so I can display the data from the matched record. select movietable.id, movietable.songname, songs.id, songs.songname FROM movietable, songs WHERE movietable.songname = songs.songname; Results: Empty set (0.19 sec) This doesn't work either select movietable.songname, movietable.ID, songs.songname, songs.id FROM movietable, songs WHERE movietable.songname = Empire; Yet, I know that movietable.songname contains a song called Empire and movietable.songs also contains a song called Empire How do I match the two strings? Running Linux with MySQL-3.23.32-1.7 - 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 - 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
mysql perl query question
I am trying to execute the following mysql query in a perl script...
my $sth = $dbh-prepare(select * from servers);
$sth-execute();
my @row;
while ( @row = $sth-fetchrow_arrayref())
{
foreach $row(@row)
{
push @SelectedArray, $row\n\n;
}
}
print @SelectedArray;
And I am getting the following error...
DBD::mysql::st fetchrow_arrayref failed: fetch() without execute() at
./server_display.pl line 20
which is the while loop line.
I set DBI trace and I can see the data in the table, just when it reaches the fetch it
bombs on me...
Sorry if this belongs more on the perl users list, but I thought everyoen would enjoy
a DBI refresher...Any ideas?
Thanks,
Taylor
Taylor Lewick
Unix System Administrator
Fortis Benefits
816 881 6073
Help Wanted. Seeking Telepath...
You Know where to apply.
Please Note
The information in this E-mail message is legally privileged
and confidential information intended only for the use of the
individual(s) named above. If you, the reader of this message,
are not the intended recipient, you are hereby notified that
you should not further disseminate, distribute, or forward this
E-mail message. If you have received this E-mail in error,
please notify the sender. Thank you
*
-
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: ENUM query question
Maybe you should say
WHERE category LIKE 'Gambling'
- Original Message -
From: Jeff Field [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, June 12, 2002 11:42 AM
Subject: ENUM query question
I have a quick question regarding queries that involve values in ENUM
columns. Here's the scenario:
I have an ENUM column (category) that can have the values ('Gambling',
'Geographic', 'Medical', 'Sports'). Most of the records have only one of
the category descriptions but some are categorized in two or more of the
category descriptions.
When I do a query and my WHERE clause is
WHERE category = 'Gambling'
it only pulls the records for which 'Gambling' is the only value. To get
*all* the records, including the records where the category value might be
(Gambling, Sports), I have to use the wildcard, as in
WHERE category = '%Gambling%'
Here's the question:
Although this may be exactly what I have to do to get those records, my own
personal expectation was that the first query (without the wildcards) should
have been fine. Is having to use wildcards with an ENUM column in the WHERE
clause correct? Or, am I missing something here?
Thanks!
Jeff
-
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: ENUM query question
At 12:42 -0500 6/12/02, Jeff Field wrote:
I have a quick question regarding queries that involve values in ENUM
columns. Here's the scenario:
I have an ENUM column (category) that can have the values ('Gambling',
'Geographic', 'Medical', 'Sports'). Most of the records have only one of
the category descriptions but some are categorized in two or more of the
category descriptions.
That's impossible. It sounds like you really mean SET, not ENUM.
When I do a query and my WHERE clause is
WHERE category = 'Gambling'
it only pulls the records for which 'Gambling' is the only value. To get
*all* the records, including the records where the category value might be
(Gambling, Sports), I have to use the wildcard, as in
WHERE category = '%Gambling%'
Here's the question:
Although this may be exactly what I have to do to get those records, my own
personal expectation was that the first query (without the wildcards) should
have been fine. Is having to use wildcards with an ENUM column in the WHERE
clause correct? Or, am I missing something here?
Thanks!
Jeff
-
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: SET query question (was ENUM query question)
Yes! My apologies; didn't have my coffee this morning. The column is of
type SET.
Jeff
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 12, 2002 12:50 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: ENUM query question
At 12:42 -0500 6/12/02, Jeff Field wrote:
I have a quick question regarding queries that involve values in ENUM
columns. Here's the scenario:
I have an ENUM column (category) that can have the values ('Gambling',
'Geographic', 'Medical', 'Sports'). Most of the records have only one of
the category descriptions but some are categorized in two or more of the
category descriptions.
That's impossible. It sounds like you really mean SET, not ENUM.
When I do a query and my WHERE clause is
WHERE category = 'Gambling'
it only pulls the records for which 'Gambling' is the only value. To get
*all* the records, including the records where the category
value might be
(Gambling, Sports), I have to use the wildcard, as in
WHERE category = '%Gambling%'
Here's the question:
Although this may be exactly what I have to do to get those
records, my own
personal expectation was that the first query (without the
wildcards) should
have been fine. Is having to use wildcards with an ENUM column
in the WHERE
clause correct? Or, am I missing something here?
Thanks!
Jeff
-
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
Query question
This query seems to be quite easy, but I still cannot figure out how to do it. I have a randomly ordered table. And I want to SELECT that table by ORDERing it in specific row, and when I'm done I want to find out numeric position of a raw where the first raw is 1, second is 2 etc. Of course I could iterate over each row. But I want to do it for a specific raw only. Any ideas? - 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 question in join table with null
I have been battling with a complex query that us giving me a tremendous headache.. I have a MASTER table with 7 other tables (I can not change the database) The master table contains values (Key's) which I then need to lookup in another table that has 2 fields, the Key and a Description field. Everything works fine, but when one field in the master table has a null value, the entire query craps out. In MSSQL, I would use a IF THEN ELSE statement to get around this.. Is there such a feature in MySQL (running v3.23)? My Query is below: Select MASTER.N_NUMBER, MASTER.SERIAL_NUMBER, MASTER.YEAR_MFR, MASTER.REGISTRANT_NAME, MASTER.STREET1, MASTER.STREET2, MASTER.REGISTRANT_CITY, MASTER.REGISTRANT_STATE, MASTER.REGISTRANT_ZIP_CODE, MASTER.REGISTRANT_REGION, MASTER.COUNTY_MAIL, MASTER.COUNTRY, MASTER.LAST_ACTION_DATE, MASTER.CERTIFICATE_ISSUE_DATE, MASTER.APPROVED_OPERATION_CODES, MASTER.TYPE_ENGINE, MASTER.MODE_S_CODE, MASTER.FRACT_OWNER, ACFTREF.MANUFACTURER_NAME, ACFTREF.MODEL_NAME, ACFTREF.AIRCRAFT_TYPE, ACFTREF.ENGINE_TYPE, ACFTREF.AIRCRAFT_CATEGORY, ACFTREF.AMATEUR_CERTIFICATION, ACFTREF.NUMBER_ENGINES, ACFTREF.NUMBER_SEATS, ACFTREF.AIRCRAFT_WEIGHT, ACFTREF.AIRCRAFT_CRUISING_SPEED, AIRCRAFT_CODES.DESCRIPTION, AIRWORTHINESS_CODES.DESCRIPTION, ENGINE.ENGINE_MANUFACTURER, ENGINE.ENGINE_MODEL_NAME, ENGINE.ENGINE_TYPE, ENGINE.ENGINE_HORSEPOWER_THRUST, ENGINE.FUEL_CONSUMED, REGISTRANT_CODES.DESCRIPTION, MASTER_STATUS_CODES.DESCRIPTION From MASTER MASTER, ACFTREF ACFTREF, AIRCRAFT_CODES AIRCRAFT_CODES, AIRWORTHINESS_CODES AIRWORTHINESS_CODES, ENGINE ENGINE, REGISTRANT_CODES REGISTRANT_CODES, MASTER_STATUS_CODES MASTER_STATUS_CODES Where ( MASTER.AIRCRAFT_MFR_MODEL = ACFTREF.MANUFACTURER_MODEL_SERIES AndMASTER.TYPE_AIRCRAFT = AIRCRAFT_CODES.AIRCRAFT_CODE AndMASTER.AIRWORTHINESS_CLASSIFICATION = AIRWORTHINESS_CODES.AIRWORTHINESS_CODE AndMASTER.ENGINE_MFR_MODEL = ENGINE.ENGINE_CODE AndMASTER.TYPE_REGISTRANT = REGISTRANT_CODES.REGISTRANT_CODE AndMASTER.STATUS = MASTER_STATUS_CODES.STATUS_CODE ) AND MASTER.N_NUMBER = '3868J' The MASTER.STATUS field is the one that could sometimes contain a NULL.. Any help would be greatly appreciated! Dennis - 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 question average per hour per agent
I'm having a problem in a query that I'm trying to do. I have a database of tickets and I want to get the average numbers of tickets created per agent per hour over a date range in the format: Hour | Avg per agent This is what I have so far: SELECT HOUR(created) AS hourcreated, COUNT(*) AS sum-per-hour FROM remedy WHERE (created='2002-4-25' AND created='2002-04-30') GROUP BY HOUR(created) This query only gives the SUM of tickets per hour. Is there a way to get the number of distinct submitters per hour so I can divide that by count to get avg/hour? perhaps I'm not thinking straight and I'm missing something -- any ideas?? Thanks! graeme - 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: Query question average per hour per agent
SELECT COUNT(DISTINCT(submitter)) AS submitters, HOUR(created) AS hourcreated, COUNT(*) AS sum-per-hour FROM remedy WHERE (created='2002-4-25' AND created='2002-04-30') GROUP BY HOUR(created); Is this what you want? Gurhan -Original Message- From: Graeme B. Davis [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 11:52 AM To: [EMAIL PROTECTED] Subject: Query question average per hour per agent I'm having a problem in a query that I'm trying to do. I have a database of tickets and I want to get the average numbers of tickets created per agent per hour over a date range in the format: Hour | Avg per agent This is what I have so far: SELECT HOUR(created) AS hourcreated, COUNT(*) AS sum-per-hour FROM remedy WHERE (created='2002-4-25' AND created='2002-04-30') GROUP BY HOUR(created) This query only gives the SUM of tickets per hour. Is there a way to get the number of distinct submitters per hour so I can divide that by count to get avg/hour? perhaps I'm not thinking straight and I'm missing something -- any ideas?? Thanks! graeme - 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: Query question average per hour per agent
[snip] I have a database of tickets and I want to get the average numbers of tickets created per agent per hour over a date range in the format: Hour | Avg per agent This is what I have so far: SELECT HOUR(created) AS hourcreated, COUNT(*) AS sum-per-hour FROM remedy WHERE (created='2002-4-25' AND created='2002-04-30') GROUP BY HOUR(created) This query only gives the SUM of tickets per hour. Is there a way to get the number of distinct submitters per hour so I can divide that by count to get avg/hour? [/snip] You probably need a crosstab query and I would need to see some of the table to help you create it. It would look something like; SELECT DATE(created) AS DateCreated, SUM(IF(HOUR(created) = '10:00', 1, 0)) as 10 am, SUM(IF(HOUR(created) = '11:00', 1, 0)) as 11 am, SUM(IF(HOUR(created) = '12:00', 1, 0)) as 12 am, SUM(IF(HOUR(created) = '13:00', 1, 0)) as 1 pm FROM remedy WHERE (created='2002-4-25' AND created='2002-04-30') GROUP BY DATE(created) would give something like; +-+---+--+-+---+ | DateCreated | 10 am |11 am | 12 am | 1 pm | +-+---+--+-+---+ | 2002-04-25 | 159 | 72 | 0 | 0 | | 2002-04-26 |28 |0 | 14 |14 | | 2002-04-27 |22 | 17 | 17 |17 | | 2002-04-28 |36 | 13 | 49 |85 | | 2002-04-29 |12 |0 | 12 |12 | | 2002-04-29 |12 | 83 | 72 |12 | +-+---+--+-+---+ HTH! Jay - 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
mysql query question
How can I delete from a table the results of a join query.. I.e, join two tables together, get the resutls, now want to delete that data from one of the tables... Do I have to create a tempory table, hold the data, and delete from the one table data matching in the temp table, or can I just combine a delete statement with my original query...? here is my query, which works.. select distinct table1.ticker_name from table1 LEFT JOIN on table2 on (table1.ticker_name = table2.ticker_name) where table2.ticker_name IS NULL; Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - 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
[Fwd: mysql query question]
Sorry, I've been too long in a list where you just answer the questioner and then the questioner writes a summary of the answers to the list. Sabine Sabine Richter wrote: Hello Taylor, as far as I see from the documentation and own trials you can just delete from table where conditions_of_this_table. So you can not delete values matching a temp table. But I think I have a possible workaround for you: 1: create a new table with the opposite of your question, i.e. the rows both tables have in common: -- create table interim select table1.* from table1, table2 where table1.ticker_name = table2.ticker_name; 2. delete table1 3. rename table interim to table1 I think that will do what you want. Bye Sabine Taylor Lewick wrote: How can I delete from a table the results of a join query.. I.e, join two tables together, get the resutls, now want to delete that data from one of the tables... Do I have to create a tempory table, hold the data, and delete from the one table data matching in the temp table, or can I just combine a delete statement with my original query...? here is my query, which works.. select distinct table1.ticker_name from table1 LEFT JOIN on table2 on (table1.ticker_name = table2.ticker_name) where table2.ticker_name IS NULL; Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - 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: easy query question
select id, shortdescription, title from reviews where title REGEXP '^[0-9#$].*' ORDER BY title ASC -Original Message- From: Jay Paulson [mailto:[EMAIL PROTECTED]] Sent: Monday, April 01, 2002 6:32 PM To: [EMAIL PROTECTED] Subject: easy query question I want to do a query that will find all the titles I have in my db that start with numbers and ambigious characters (i.e. 0-9, , #, $ etc...). My query is below, however I don't know what to change the a too in order for it to return what I just described. select id, shortdescription, title from reviews where title LIKE UPPER(\a%\) ORDER BY title ASC Thanks for any help. - 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
easy query question
I want to do a query that will find all the titles I have in my db that start with numbers and ambigious characters (i.e. 0-9, , #, $ etc...). My query is below, however I don't know what to change the a too in order for it to return what I just described. select id, shortdescription, title from reviews where title LIKE UPPER(\a%\) ORDER BY title ASC Thanks for any help. - 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
SQL query question - using LIKE
I have a simple search form that allows the user to search a contact db based on criteria that they choose. Search by - first name - text box last name - text box region - drop down loan officer - drop down I want the user to be able to receive results if they choose all possible criteria or just one criteria. My question is about query structure. How do I query the database when I have multiple criteria selected? Any help is appreciated. Mark - 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: SQL query question - using LIKE
Hi $query = 'SELECT * FROM Table WHERE FirstName LIKE %'.$firstname.'%' ; if($lastname)$query.= ', AND LastName LIKE %'.$lastname.'%'; if($region)$query.= ', AND Region LIKE %'.$region.'%'; if($loan_officer)$query.= ', AND Loan_officer LIKE %'.$loan_officer.'%'; etc. $mysql_result = mysql_query($query, $mysql_link); HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: 28 March 2002 17:21 To: MySQL Subject: SQL query question - using LIKE I have a simple search form that allows the user to search a contact db based on criteria that they choose. Search by - first name - text box last name - text box region - drop down loan officer - drop down I want the user to be able to receive results if they choose all possible criteria or just one criteria. My question is about query structure. How do I query the database when I have multiple criteria selected? Any help is appreciated. Mark - 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: SQL query question - using LIKE
Mark, Peter, The query below assumes that the user will search of FirstName and none or more of the other fields - which was not how the question reads (to me). To answer the question it would be useful to know how you are accessing MySQL - are you using PHP (as per example code below) for example? When the form data is received it is (very) necessary to 'clean' and validate the data. Part of this process involves the question was this field filled out?. Thus front end-processing is the time for the query's WHERE clause to be built up. The last question is going to be has at least one field been filled out?! The methodology of Peter's reply still applies. There are a number of tutorials available on various sites (but can't point you at one because don't know which tool you're using!!!). Would certainly recommend researching a few... Regards, =dn $query = 'SELECT * FROM Table WHERE FirstName LIKE %'.$firstname.'%' ; if($lastname)$query.= ', AND LastName LIKE %'.$lastname.'%'; if($region)$query.= ', AND Region LIKE %'.$region.'%'; if($loan_officer)$query.= ', AND Loan_officer LIKE %'.$loan_officer.'%'; etc. $mysql_result = mysql_query($query, $mysql_link); HTH Peter --- Excellence in internet and open source software --- Sunmaia www.sunmaia.net [EMAIL PROTECTED] tel. 0121-242-1473 --- -Original Message- From: Mark Stringham [mailto:[EMAIL PROTECTED]] Sent: 28 March 2002 17:21 To: MySQL Subject: SQL query question - using LIKE I have a simple search form that allows the user to search a contact db based on criteria that they choose. Search by - first name - text box last name - text box region - drop down loan officer - drop down I want the user to be able to receive results if they choose all possible criteria or just one criteria. My question is about query structure. How do I query the database when I have multiple criteria selected? Any help is appreciated. Mark - 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 - 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: SQL query question - using LIKE
DL -
Points well taken -
I am using php and doing simple validation
EX - if ($fname !=) {
add fname string to search variable;
}
And if none of the fields have been filled out I'll return an error msg.
As you mentioned, Peter's logic still applies here and it has given me
enough to work with.
But I am certainly open for more suggestion/ feedback.
Thanks
Mark
-Original Message-
From: DL Neil [EMAIL PROTECTED]
To: Peter Lovatt [EMAIL PROTECTED]; Mark Stringham
[EMAIL PROTECTED]; MySQL [EMAIL PROTECTED]
Date: Thursday, March 28, 2002 11:52 AM
Subject: Re: SQL query question - using LIKE
Mark, Peter,
The query below assumes that the user will search of FirstName and none
or more of the other fields - which was not how the question reads (to
me).
To answer the question it would be useful to know how you are accessing
MySQL - are you using PHP (as per example code below) for example?
When the form data is received it is (very) necessary to 'clean' and
validate the data. Part of this process involves the question was this
field filled out?. Thus front end-processing is the time for the
query's WHERE clause to be built up. The last question is going to be
has at least one field been filled out?!
The methodology of Peter's reply still applies. There are a number of
tutorials available on various sites (but can't point you at one because
don't know which tool you're using!!!). Would certainly recommend
researching a few...
Regards,
=dn
$query = 'SELECT *
FROM Table
WHERE
FirstName LIKE %'.$firstname.'%' ;
if($lastname)$query.= ', AND LastName LIKE
%'.$lastname.'%';
if($region)$query.= ', AND Region LIKE %'.$region.'%';
if($loan_officer)$query.= ', AND Loan_officer LIKE
%'.$loan_officer.'%';
etc.
$mysql_result = mysql_query($query, $mysql_link);
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
www.sunmaia.net
[EMAIL PROTECTED]
tel. 0121-242-1473
---
-Original Message-
From: Mark Stringham [mailto:[EMAIL PROTECTED]]
Sent: 28 March 2002 17:21
To: MySQL
Subject: SQL query question - using LIKE
I have a simple search form that allows the user to search a
contact db
based on criteria that they choose.
Search by -
first name - text box
last name - text box
region - drop down
loan officer - drop down
I want the user to be able to receive results if they choose all
possible
criteria or just one criteria.
My question is about query structure. How do I query the database
when I
have multiple criteria selected?
Any help is appreciated.
Mark
-
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
-
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: SQL query question - using LIKE
Mark,
Tutorials: Start at the PHP home page and look for the links page.
DevShed would be a good start.
Regards,
=dn
- Original Message -
From: Mark Stringham [EMAIL PROTECTED]
To: DL Neil [EMAIL PROTECTED]; Peter Lovatt
[EMAIL PROTECTED]; MySQL [EMAIL PROTECTED]
Sent: 28 March 2002 19:16
Subject: Re: SQL query question - using LIKE
DL -
Points well taken -
I am using php and doing simple validation
EX - if ($fname !=) {
add fname string to search variable;
}
And if none of the fields have been filled out I'll return an error
msg.
As you mentioned, Peter's logic still applies here and it has given me
enough to work with.
But I am certainly open for more suggestion/ feedback.
Thanks
Mark
-Original Message-
From: DL Neil [EMAIL PROTECTED]
To: Peter Lovatt [EMAIL PROTECTED]; Mark Stringham
[EMAIL PROTECTED]; MySQL [EMAIL PROTECTED]
Date: Thursday, March 28, 2002 11:52 AM
Subject: Re: SQL query question - using LIKE
Mark, Peter,
The query below assumes that the user will search of FirstName and
none
or more of the other fields - which was not how the question reads
(to
me).
To answer the question it would be useful to know how you are
accessing
MySQL - are you using PHP (as per example code below) for example?
When the form data is received it is (very) necessary to 'clean' and
validate the data. Part of this process involves the question was
this
field filled out?. Thus front end-processing is the time for the
query's WHERE clause to be built up. The last question is going to be
has at least one field been filled out?!
The methodology of Peter's reply still applies. There are a number of
tutorials available on various sites (but can't point you at one
because
don't know which tool you're using!!!). Would certainly recommend
researching a few...
Regards,
=dn
$query = 'SELECT *
FROM Table
WHERE
FirstName LIKE %'.$firstname.'%' ;
if($lastname)$query.= ', AND LastName LIKE
%'.$lastname.'%';
if($region)$query.= ', AND Region LIKE %'.$region.'%';
if($loan_officer)$query.= ', AND Loan_officer LIKE
%'.$loan_officer.'%';
etc.
$mysql_result = mysql_query($query, $mysql_link);
HTH
Peter
---
Excellence in internet and open source software
---
Sunmaia
www.sunmaia.net
[EMAIL PROTECTED]
tel. 0121-242-1473
---
-Original Message-
From: Mark Stringham [mailto:[EMAIL PROTECTED]]
Sent: 28 March 2002 17:21
To: MySQL
Subject: SQL query question - using LIKE
I have a simple search form that allows the user to search a
contact db
based on criteria that they choose.
Search by -
first name - text box
last name - text box
region - drop down
loan officer - drop down
I want the user to be able to receive results if they choose all
possible
criteria or just one criteria.
My question is about query structure. How do I query the
database
when I
have multiple criteria selected?
Any help is appreciated.
Mark
-
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
-
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
Delete Query Question
Hope somebody can help me on this one At present I have two tables in my database. The data in question is built around credit card transactions. Tables Structure Headers Table ID (Primary Key used by the TRANSACTIONID field in the Transaction table) HEADERDETAILS blah, blah Transaction Table ID (Primary Key) TRANSACTIONID (Foreign Key into the Headers table) TRANSACTIONDETAILS blah,blah TRANSACTIONDATE I wish to delete all records from the headers table for a given date AND the associated records in the transaction table. After looking through the MySQL manual I discovered that deleting from multiple tables using a join is not supported. Has anybody any ideas I can use to remove the data ??? (and some REAL life queries cause I`m still just a beginner :)) p.s. If this is not clear please say so and I will elaborate :) This message contains information that may be privileged or confidential and is the property of the Cap Gemini Ernst Young Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message . - 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
Delete Query Question
Edwards, Friday, February 22, 2002, 12:58:23 PM, you wrote: Eeoen Hope somebody can help me on this one Eeoen At present I have two tables in my database. The data in question is built Eeoen around credit card transactions. Eeoen Tables Structure Eeoen Headers Table Eeoen ID (Primary Key used by the TRANSACTIONID field in the Transaction table) Eeoen HEADERDETAILS blah, blah Eeoen Transaction Table Eeoen ID (Primary Key) Eeoen TRANSACTIONID (Foreign Key into the Headers table) Eeoen TRANSACTIONDETAILS blah,blah Eeoen TRANSACTIONDATE Eeoen I wish to delete all records from the headers table for a given date AND the Eeoen associated records in the transaction table. After looking through the Eeoen MySQL manual I discovered that deleting from multiple tables using a join is Eeoen not supported. Has anybody any ideas I can use to remove the data ??? Eeoen (and some REAL life queries cause I`m still just a beginner :)) Eeoen p.s. If this is not clear please say so and I will elaborate :) Try to use CONCAT() function in SELECT statement. Look comments in the manual at: http://www.mysql.com/doc/D/E/DELETE.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: basic query question
to create a second instance of the same table and this will probably show the relationship twice SELECT p1.lname, p1.fname, p2.lname, p2.fname FROM members AS 'p1' LEFT JOIN members AS 'p2' ON p1.engagedto = p2.id not sure if you can do the LEFT JOIN table AS 'table2' or not, can't get to mysql.com to check the documentation :( it will be something similar however your table being in this structure of course +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 18| | 273 | Simmons | Maria | 78| | 221 | Papa | Sharla | 123 | | 18 | Biehl | Ruth| 131 | | 302 | Vance | Alicia | 204 | | 123 | Goettl| Christopher | 221 | | 78 | Ellsworth | Morgan | 273 | | 204 | Millet| David | 302 | +-+---+-+---+ -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 2:53 PM To: Roger Karnouk; [EMAIL PROTECTED] Subject: Re: basic query question Okay, so I do this and all I end up with is +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 131 | | 273 | Simmons | Maria | 273 | | 221 | Papa | Sharla | 221 | | 18 | Biehl | Ruth| 18| | 302 | Vance | Alicia | 302 | | 123 | Goettl| Christopher | 123 | | 78 | Ellsworth | Morgan | 78| | 204 | Millet| David | 204 | +-+---+-+---+ 8 rows in set (0.00 sec) The returning query doesn't seem very helpful. I guess what I want is mysql to return the results with each person they are engaged to in order. I added the p1.id and p1.engagedto fields. mysql select p1.id, p1.lname, p1.fname, p1.engagedto, p2.engagedto from members p1, members p2 where p1.id = p2.engagedto; +-+---+-+---+---+ | id | lname | fname | engagedto | engagedto | +-+---+-+---+---+ | 131 | Hallows | Samuel | 18| 131 | | 273 | Simmons | Maria | 78| 273 | | 221 | Papa | Sharla | 123 | 221 | | 18 | Biehl | Ruth| 131 | 18| | 302 | Vance | Alicia | 204 | 302 | | 123 | Goettl| Christopher | 221 | 123 | | 78 | Ellsworth | Morgan | 273 | 78| | 204 | Millet| David | 302 | 204 | +-+---+-+---+---+ 8 rows in set (0.01 sec) This at least shows me the info I had in my original table. If I looked at it I could see that id# 131 should match up with id# 18 and such. I guess I'm looking for a query that will match these to up either in the same row or one after another. Any help is SOOO appreciated! Dean - Original Message - From: Roger Karnouk [EMAIL PROTECTED] To: Dean Householder [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 12:17 PM Subject: RE: basic query question you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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
basic query question
I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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: basic query question
At 19.02.2002 10:22, you wrote: I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic Try this, even if I´m careful due to my bad day, see todays threads ;-) select id, lname, fname, pic from database where engagedto=id; The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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: basic query question
assuming that engaedto refers to an id, your query might be: SELECT a.lname,a.fname,b.engagedto FROM mydata a LEFT JOIN mydata b ON(a.id=b.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 11:22 AM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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: basic query question
select id, lname, fname, pic from database where engagedto=id; Sorry, mate, that won't work. That will find folks who are engaged to themselves, i.e., narcissists -Original Message- From: Oliver Heinisch [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 11:30 AM To: [EMAIL PROTECTED] Subject: Re: basic query question At 19.02.2002 10:22, you wrote: I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic Try this, even if I´m careful due to my bad day, see todays threads ;-) select id, lname, fname, pic from database where engagedto=id; The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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: basic query question
you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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: basic query question
Okay, so I do this and all I end up with is +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 131 | | 273 | Simmons | Maria | 273 | | 221 | Papa | Sharla | 221 | | 18 | Biehl | Ruth| 18| | 302 | Vance | Alicia | 302 | | 123 | Goettl| Christopher | 123 | | 78 | Ellsworth | Morgan | 78| | 204 | Millet| David | 204 | +-+---+-+---+ 8 rows in set (0.00 sec) The returning query doesn't seem very helpful. I guess what I want is mysql to return the results with each person they are engaged to in order. I added the p1.id and p1.engagedto fields. mysql select p1.id, p1.lname, p1.fname, p1.engagedto, p2.engagedto from members p1, members p2 where p1.id = p2.engagedto; +-+---+-+---+---+ | id | lname | fname | engagedto | engagedto | +-+---+-+---+---+ | 131 | Hallows | Samuel | 18| 131 | | 273 | Simmons | Maria | 78| 273 | | 221 | Papa | Sharla | 123 | 221 | | 18 | Biehl | Ruth| 131 | 18| | 302 | Vance | Alicia | 204 | 302 | | 123 | Goettl| Christopher | 221 | 123 | | 78 | Ellsworth | Morgan | 273 | 78| | 204 | Millet| David | 302 | 204 | +-+---+-+---+---+ 8 rows in set (0.01 sec) This at least shows me the info I had in my original table. If I looked at it I could see that id# 131 should match up with id# 18 and such. I guess I'm looking for a query that will match these to up either in the same row or one after another. Any help is SOOO appreciated! Dean - Original Message - From: Roger Karnouk [EMAIL PROTECTED] To: Dean Householder [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 12:17 PM Subject: RE: basic query question you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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 - 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: basic query question
You're very close: mysql select p1.lname, p1.fname, p2.lname as engaged_lname, p2.fname as engaged_fname from members p1, members p2 where p1.id = p2.engagedto; but the REAL question is What do you want out of the query? -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 1:53 PM To: Roger Karnouk; [EMAIL PROTECTED] Subject: Re: basic query question Okay, so I do this and all I end up with is +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 131 | | 273 | Simmons | Maria | 273 | | 221 | Papa | Sharla | 221 | | 18 | Biehl | Ruth| 18| | 302 | Vance | Alicia | 302 | | 123 | Goettl| Christopher | 123 | | 78 | Ellsworth | Morgan | 78| | 204 | Millet| David | 204 | +-+---+-+---+ 8 rows in set (0.00 sec) The returning query doesn't seem very helpful. I guess what I want is mysql to return the results with each person they are engaged to in order. I added the p1.id and p1.engagedto fields. mysql select p1.id, p1.lname, p1.fname, p1.engagedto, p2.engagedto from members p1, members p2 where p1.id = p2.engagedto; +-+---+-+---+---+ | id | lname | fname | engagedto | engagedto | +-+---+-+---+---+ | 131 | Hallows | Samuel | 18| 131 | | 273 | Simmons | Maria | 78| 273 | | 221 | Papa | Sharla | 123 | 221 | | 18 | Biehl | Ruth| 131 | 18| | 302 | Vance | Alicia | 204 | 302 | | 123 | Goettl| Christopher | 221 | 123 | | 78 | Ellsworth | Morgan | 273 | 78| | 204 | Millet| David | 302 | 204 | +-+---+-+---+---+ 8 rows in set (0.01 sec) This at least shows me the info I had in my original table. If I looked at it I could see that id# 131 should match up with id# 18 and such. I guess I'm looking for a query that will match these to up either in the same row or one after another. Any help is SOOO appreciated! Dean - Original Message - From: Roger Karnouk [EMAIL PROTECTED] To: Dean Householder [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 12:17 PM Subject: RE: basic query question you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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 - 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
Re: basic query question
Thank you all so much for helping me with this query. It works! Best of all, now I understand how to join tables and such! Thanks again! - Original Message - From: Rick Emery [EMAIL PROTECTED] To: 'Dean Householder' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 1:04 PM Subject: RE: basic query question You're very close: mysql select p1.lname, p1.fname, p2.lname as engaged_lname, p2.fname as engaged_fname from members p1, members p2 where p1.id = p2.engagedto; but the REAL question is What do you want out of the query? -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 1:53 PM To: Roger Karnouk; [EMAIL PROTECTED] Subject: Re: basic query question Okay, so I do this and all I end up with is +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 131 | | 273 | Simmons | Maria | 273 | | 221 | Papa | Sharla | 221 | | 18 | Biehl | Ruth| 18| | 302 | Vance | Alicia | 302 | | 123 | Goettl| Christopher | 123 | | 78 | Ellsworth | Morgan | 78| | 204 | Millet| David | 204 | +-+---+-+---+ 8 rows in set (0.00 sec) The returning query doesn't seem very helpful. I guess what I want is mysql to return the results with each person they are engaged to in order. I added the p1.id and p1.engagedto fields. mysql select p1.id, p1.lname, p1.fname, p1.engagedto, p2.engagedto from members p1, members p2 where p1.id = p2.engagedto; +-+---+-+---+---+ | id | lname | fname | engagedto | engagedto | +-+---+-+---+---+ | 131 | Hallows | Samuel | 18| 131 | | 273 | Simmons | Maria | 78| 273 | | 221 | Papa | Sharla | 123 | 221 | | 18 | Biehl | Ruth| 131 | 18| | 302 | Vance | Alicia | 204 | 302 | | 123 | Goettl| Christopher | 221 | 123 | | 78 | Ellsworth | Morgan | 273 | 78| | 204 | Millet| David | 302 | 204 | +-+---+-+---+---+ 8 rows in set (0.01 sec) This at least shows me the info I had in my original table. If I looked at it I could see that id# 131 should match up with id# 18 and such. I guess I'm looking for a query that will match these to up either in the same row or one after another. Any help is SOOO appreciated! Dean - Original Message - From: Roger Karnouk [EMAIL PROTECTED] To: Dean Householder [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 12:17 PM Subject: RE: basic query question you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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
Avanced query question
Hello, I have a question about a query. We have a guestbook and I want to do some statistics for the messages in it. So I've created a query that looks like this. select hour(msg_date_time), count(*) from messages where user_id = 'almar' group by hour(msg_date_time) It returns the hour and the number of messages posted in that hour. However, there are hours where no messages have been posted, so the result would look like this: hour msg_count 0 10 1 15 3 6 So in the example between 2 a clock and 3 a clock no messages have been posted. But I do want it to return 0 for hour 2. I know that my query will never return that result. We are using perl DBI. Does anyone have an idea? Kind regards Almar van Pel - 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: Avanced query question
Hello Almar I have a question about a query. We have a guestbook and I want to do some statistics for the messages in it. So I've created a query that looks like this. select hour(msg_date_time), count(*) from messages where user_id = 'almar' group by hour(msg_date_time) It returns the hour and the number of messages posted in that hour. However, there are hours where no messages have been posted, so the result would look like this: hour msg_count 0 10 1 15 3 6 So in the example between 2 a clock and 3 a clock no messages have been posted. But I do want it to return 0 for hour 2. I know that my query will never return that result. We are using perl DBI. Does anyone have an idea? Data cannot be retrieved from the database, if it has not first been stored there! Your mind holds a firm relationship between a particular time and the number of hours in a day - the computer does not (appear) to have the latter information. Consider: a) add a 'construction' table with the row-values 0 through 11 (or 23) and use it to do a left join against the messages tbl; b) if the database is not busy, use perl to fire off 12 (or 24) separate queries. Regards, =dn - 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: Avanced query question
* DL Neil Consider: a) add a 'construction' table with the row-values 0 through 11 (or 23) and use it to do a left join against the messages tbl; b) if the database is not busy, use perl to fire off 12 (or 24) separate queries. c) use perl to 'fill in the holes', putting zero values in the 'empty slots' of an array 0-23 -- Roger query - 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
Avanced query question
Hello, I have a question about a query. We have a guestbook and I want to do some statistics for the messages in it. So I've created a query that looks like this. select hour(msg_date_time), count(*) from messages where user_id = 'almar' group by hour(msg_date_time) It returns the hour and the number of messages posted in that hour. However, there are hours where no messages have been posted, so the result would look like this: hour msg_count 0 10 1 15 3 6 So in the example between 2 a clock and 3 a clock no messages have been posted. But I do want it to return 0 for hour 2. I know that my query will never return that result. We are using perl DBI. Does anyone have an idea? Kind regards Almar van Pel - 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: Avanced query question
Hello Almar I have a question about a query. We have a guestbook and I want to do some statistics for the messages in it. So I've created a query that looks like this. select hour(msg_date_time), count(*) from messages where user_id = 'almar' group by hour(msg_date_time) It returns the hour and the number of messages posted in that hour. However, there are hours where no messages have been posted, so the result would look like this: hour msg_count 0 10 1 15 3 6 So in the example between 2 a clock and 3 a clock no messages have been posted. But I do want it to return 0 for hour 2. I know that my query will never return that result. We are using perl DBI. Does anyone have an idea? Data cannot be retrieved from the database, if it has not first been stored there! Your mind holds a firm relationship between a particular time and the number of hours in a day - the computer does not (appear) to have the latter information. Consider: a) add a 'construction' table with the row-values 0 through 11 (or 23) and use it to do a left join against the messages tbl; b) if the database is not busy, use perl to fire off 12 (or 24) separate queries. Regards, =dn - 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: Avanced query question
* DL Neil Consider: a) add a 'construction' table with the row-values 0 through 11 (or 23) and use it to do a left join against the messages tbl; b) if the database is not busy, use perl to fire off 12 (or 24) separate queries. c) use perl to 'fill in the holes', putting zero values in the 'empty slots' of an array 0-23 -- Roger query - 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 question
I am trying to validate a user in a login form. There are 3 things that must be true. User login name = email_login (from User table) User password = password (from User Table) User must be active: activeuser (from Owner table) = 'Y' I don't know why I'm having so much difficulty with this SQL statement, but can anyone help? Steve - 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 Question Additional Info
I am trying to validate a user in a login form. There are 3 things that must be true. User login name = email_login (from User table) User password = password (from User Table) User must be active: activeuser (from Owner table) = 'Y' The Owner table does have a common field with the User Table. (NameID) I don't know why I'm having so much difficulty with this SQL statement, but can anyone help? Steve - 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: Query question
I don't get the question..
you just have to compare the values out from the database with the
values from the user input.
my $sth = $dbh-prepare(SELECT usremail, usrStatus from users where
usrName = ?);
$sth-execute(param{'name'});
my ($email,$status) = ($sth-fetchrow_array);
print invalid login if (param{email} ne $email || $active ne 'Y');
Something like that, not tested at all.
HTH
Etienne
Steve Osborne wrote:
I am trying to validate a user in a login form.
There are 3 things that must be true.
User login name = email_login (from User table)
User password = password (from User Table)
User must be active: activeuser (from Owner table) = 'Y'
I don't know why I'm having so much difficulty with this SQL statement, but
can anyone help?
Steve
-
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
--
Etienne Marcotte
Specifications Management - Quality Control
Imperial Tobacco Ltd. - Montreal (Qc) Canada
514.932.6161 x.4001
-
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: Query question
$query = SELECT User.* FROM User,Owner WHERE email_login=$login_name
password=PASSWORD($password) activeuser=\Y\ ;
$result = mysql($query) or die(Error: .mysql_error());
if( mysql_num_rows($result) != 1 )
{
...not an active/valid user ...
}
-Original Message-
From: Steve Osborne [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 07, 2001 3:27 PM
To: MySQL (E-mail)
Subject: Query question
I am trying to validate a user in a login form.
There are 3 things that must be true.
User login name = email_login (from User table)
User password = password (from User Table)
User must be active: activeuser (from Owner table) = 'Y'
I don't know why I'm having so much difficulty with this SQL statement, but
can anyone help?
Steve
-
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: Query question
Oops, sorry missed the fact that it's two different tables. Try this...
$result=mysql_db_query(database_name, Select user.email_login,
user.password, owner.activeuser from user, owner where email_login like
'$name' and password like '$password' and user.nameid like owner.nameid;);
$numrows=mysql_affected_rows();
$data = mysql_fetch_array($result);
if(($numrows=1)($data[owner.activeuser]=='Y')){
login_script
}
Jason
-Original Message-
From: Michael, Jason
Sent: Friday, December 07, 2001 3:50 PM
To: 'Steve Osborne'
Subject: RE: Query question
Try this...
$result=mysql_db_query(database_name, Select * from table_name where
email_login like 'name' and password like 'password';);
$numrows=mysql_affected_rows();
$data = mysql_fetch_array($result);
if(($numrows=1)($data[activeuser]=='Y')){
login_script
}
Jason
-Original Message-
From: Steve Osborne [SMTP:[EMAIL PROTECTED]]
Sent: Friday, December 07, 2001 4:27 PM
To: MySQL (E-mail)
Subject:Query question
I am trying to validate a user in a login form.
There are 3 things that must be true.
User login name = email_login (from User table)
User password = password (from User Table)
User must be active: activeuser (from Owner table) = 'Y'
I don't know why I'm having so much difficulty with this SQL
statement, but
can anyone help?
Steve
-
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: Query Question Additional Info
Thanks for all your suggestions, I apparently had a brain cramp. Problem solved. Thanks, Steve - Original Message - From: Steve Osborne [EMAIL PROTECTED] To: MySQL (E-mail) [EMAIL PROTECTED] Sent: Friday, December 07, 2001 1:36 PM Subject: Query Question Additional Info I am trying to validate a user in a login form. There are 3 things that must be true. User login name = email_login (from User table) User password = password (from User Table) User must be active: activeuser (from Owner table) = 'Y' The Owner table does have a common field with the User Table. (NameID) I don't know why I'm having so much difficulty with this SQL statement, but can anyone help? Steve - 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: Query question!
Does anyone know how to write a query to get out the sum of a whole
table column?
SELECT sum(column) FROM table {WHERE where-clause};
Also see the manual.
:wq!
Ralph
-
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 question!
Does anyone know how to write a query to get out the sum of a whole table column? ex. I'm playing around with a database where customers can buy stuff. I have a table where I save all the invoices, but now I want to write a query that sums upp all the prices in the price column in that table. Is this possible? And if, how??? Mikael Hultén - 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: Query question!
As a side note, be sure that you do not use a float for any columns that deal with money or you will get rounding errors. --Bill mysql Yen-Chu Chen wrote: Hi, Assume the name of the column is 'price' and the name of the table is 'table', you could use SELECT SUM(price) FROM table; On Wed, 26 Sep 2001, Micke wrote: Does anyone know how to write a query to get out the sum of a whole table column? ex. I'm playing around with a database where customers can buy stuff. I have a table where I save all the invoices, but now I want to write a query that sums upp all the prices in the price column in that table. Is this possible? And if, how??? Mikael Hultén - 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: Query question!
Hi, Assume the name of the column is 'price' and the name of the table is 'table', you could use SELECT SUM(price) FROM table; On Wed, 26 Sep 2001, Micke wrote: Does anyone know how to write a query to get out the sum of a whole table column? ex. I'm playing around with a database where customers can buy stuff. I have a table where I save all the invoices, but now I want to write a query that sums upp all the prices in the price column in that table. Is this possible? And if, how??? Mikael Hultén - 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 -- Best regards,Yen-Chu Chen [EMAIL PROTECTED] Office: (630) 840-5403, FAX: (630) 840-2968 (886)-(2)-2789-9681 (Inst. of Phys., Academia Sinica) - 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: Query question.
Not quite. We're not looking for a left join or natural left join. I'm not sure MySQL or any database can even do this, it may have to be done (rather unattractively) on the API side. The data in the two tables aren't tied by ID, and shouldn't mix w/ each other at all. To do this, all we've been able to come up with is to use the API (PHP in our case) to make 2 queries, copy them into the same array, and sort the array by timestamp. However the API's array sort isn't the quickest thing ever coded, and the SQL level is where we'd like to do the sorting. To clarify, I'll give an example of what we are trying to do: daily_notes: note_iduser_numtimestampnote 111Hello 212Goodbye 3231 Blah project_notes: note_iduser_numtimestampnote 121Working hard 215Watching others work hard We'd like a query that would return daily_notes #1 and #2 and project_notes #2. However, like I said earlier, I'm not sure SQL can do this. This list is kind of a last resort before defaulting on letting the API sort it out after the 2 queries. Ken wrote: Sounds like a left join, maybe even a natural join, which I haven't tried yet. select message from project left join daily on project.uid = daily.uid order by timestamp Check manual for syntax. Or am I missing something? - Ken [EMAIL PROTECTED] At 05:47 PM 9/6/01 -0700, Michael Garvin wrote: Ok, we just can't seem to find a solution for this problem. Thought I'd post it up to see if anyone has any input. Say you have 2 tables project_notes and daily_notes. Each table has a timestamp, and a user_id. Is there any way, in one query, to select all messages from both tables that were entered by a given user_id and sort by timestamp? This isn't the first time this problem has come up here where I work, another application of this solution (if it exists) would be to query different tables representing different realms on our radius servers to get a total usage summary across our network for a given user. - 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 question.
Ok, we just can't seem to find a solution for this problem. Thought I'd post it up to see if anyone has any input. Say you have 2 tables project_notes and daily_notes. Each table has a timestamp, and a user_id. Is there any way, in one query, to select all messages from both tables that were entered by a given user_id and sort by timestamp? This isn't the first time this problem has come up here where I work, another application of this solution (if it exists) would be to query different tables representing different realms on our radius servers to get a total usage summary across our network for a given user. - 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: Query question.
Sounds like a left join, maybe even a natural join, which I haven't tried yet. select message from project left join daily on project.uid = daily.uid order by timestamp Check manual for syntax. Or am I missing something? - Ken [EMAIL PROTECTED] At 05:47 PM 9/6/01 -0700, Michael Garvin wrote: Ok, we just can't seem to find a solution for this problem. Thought I'd post it up to see if anyone has any input. Say you have 2 tables project_notes and daily_notes. Each table has a timestamp, and a user_id. Is there any way, in one query, to select all messages from both tables that were entered by a given user_id and sort by timestamp? This isn't the first time this problem has come up here where I work, another application of this solution (if it exists) would be to query different tables representing different realms on our radius servers to get a total usage summary across our network for a given user. - 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 Question
I'm doing a query like: select Title from inv where Title LIKE '%TOMMY%' OR Content LIKE '%TOMMY%' OR Notes LIKE '%TOMMY%' order by Title What I need is to have the results that match Title LIKE '%TOMMY%' to appear first and then the rest. I have been doing 2 separate queries and filtering the first results from the second in a script. I would like to do this with a single query. Can that be done? - 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: Query Question
hi. select Title, IF(Title LIKE %TOMMY%, 1, 0) as check from inv where... ... order by check desc; hth. -ravi. -Original Message- From: Carl Schrader [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 10:30 AM To: [EMAIL PROTECTED] Subject: Query Question I'm doing a query like: select Title from inv where Title LIKE '%TOMMY%' OR Content LIKE '%TOMMY%' OR Notes LIKE '%TOMMY%' order by Title What I need is to have the results that match Title LIKE '%TOMMY%' to appear first and then the rest. I have been doing 2 separate queries and filtering the first results from the second in a script. I would like to do this with a single query. Can that be done? - 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: Query Question
Close. Now I need to get the order correct. I need the order like Tommy Tommy Lee Baseball(matched with Tommy in the Content or Notes field) World Series (matched with Tommy in the Content or Notes field) I've been playing with something like: select Title, concat(IF(Title LIKE %TOMMY%, 1, 0),LPAD(90-ASCII(left (Title,1)),2,'0')) as check from inv where (Title LIKE 'tommy%') OR (Content LIKE '%tommy%') OR (Notes LIKE '% tommy%') order by check desc but that doesn't get it close enough. Only compares the frist char in Title. I need a more robost idea... Ravi Raman wrote: hi. select Title, IF(Title LIKE %TOMMY%, 1, 0) as check from inv where... ... order by check desc; hth. -ravi. -Original Message- From: Carl Schrader [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 10:30 AM To: [EMAIL PROTECTED] Subject: Query Question I'm doing a query like: select Title from inv where Title LIKE '%TOMMY%' OR Content LIKE '%TOMMY%' OR Notes LIKE '%TOMMY%' order by Title What I need is to have the results that match Title LIKE '%TOMMY%' to appear first and then the rest. I have been doing 2 separate queries and filtering the first results from the second in a script. I would like to do this with a single query. Can that be done? - 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: Query Question
if i'm understanding what you mean, it seems like you're getting a little too complicated. select Title, IF(Title LIKE %TOMMY%, 1, 0) as check from inv where... ... order by check desc, Title this will return rows like this: +-+---+ | title | check | +-+---+ | a | 1 | | b | 1 | | c | 1 | | w | 1 | | z | 1 | | a | 0 | | h | 0 | | z | 0 | +-+---+ i.e. each subset (check=1 and check=0) sorted alphabetically. -ravi. -Original Message- From: Carl Schrader [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 12:13 PM To: Ravi Raman Cc: [EMAIL PROTECTED] Subject: Re: Query Question Close. Now I need to get the order correct. I need the order like Tommy Tommy Lee Baseball(matched with Tommy in the Content or Notes field) World Series (matched with Tommy in the Content or Notes field) I've been playing with something like: select Title, concat(IF(Title LIKE %TOMMY%, 1, 0),LPAD(90-ASCII(left (Title,1)),2,'0')) as check from inv where (Title LIKE 'tommy%') OR (Content LIKE '%tommy%') OR (Notes LIKE '% tommy%') order by check desc but that doesn't get it close enough. Only compares the frist char in Title. I need a more robost idea... Ravi Raman wrote: hi. select Title, IF(Title LIKE %TOMMY%, 1, 0) as check from inv where... ... order by check desc; hth. -ravi. -Original Message- From: Carl Schrader [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 10:30 AM To: [EMAIL PROTECTED] Subject: Query Question I'm doing a query like: select Title from inv where Title LIKE '%TOMMY%' OR Content LIKE '%TOMMY%' OR Notes LIKE '%TOMMY%' order by Title What I need is to have the results that match Title LIKE '%TOMMY%' to appear first and then the rest. I have been doing 2 separate queries and filtering the first results from the second in a script. I would like to do this with a single query. Can that be done? - 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 - 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 Question (fwd)
MYSQL DATABASE BLA BLA BLA (I really hate that filter) Ravi Raman writes: if i'm understanding what you mean, it seems like you're getting a little too complicated. select Title, IF(Title LIKE %TOMMY%, 1, 0) as check from inv where... ... order by check desc, Title Even simpler would be SELECT Title FROM ... ORDER BY !!LOCATION(Title, 'Tommy'), Title although I don't know if it would be any faster. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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
Complex Query Question
This question involves table setup questions as much as it involves a query question. Bare with me, this seems hard to explain. I have a table that includes records with 3 (relevant to this conversation) fields (actually 9..more on that later). Each of those fields have 2 other related fields. i.e.. Field Special 1 has a related field with a Start Date and another field for End date. Field Special 2 has a related field with a Start Date and another field for End date. Field Special 3 has a related field with a Start Date and another field for End date. I will probably need to add up to 3 other fields to keep track of the last shown date. Anyhow, I need to build a query that will return 5-10 Specials. I want to output this very much like an ad banner query would. That is to say, I need to keep track of the last one that was shown and output the next one. What complicates this is that there are UP TO to 3 specials per record. Some of them may not include any specials at all. Some may have 1 or 2. Special 1 should be shown before Special 2. etc. Only 1 Special from each record should be output. i.e.. Special 1 and Special 2 shouldn't be output from record #1. (Actually I would like to have that occur if there are not 5-10 other results.. but I can live without that..) The question: Is there anyway I build a single query to accomplish this? Since this is in the early design mode, I can add/delete any current fields and build it differently as needed. Alternately, I could just randomly output 1 special from each record that has one..I'd prefer the other method though... I just need some advice from some people who are more fluent in sql :) TIA! - 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: Complex Query Question
Carl Schrader wrote: Field Special 1 has a related field with a Start Date and another field for End date. Field Special 2 has a related field with a Start Date and another field for End date. Field Special 3 has a related field with a Start Date and another field for End date. IMNSHO, Any time you have a design like this where you have somedata_1, somedata_2, etc., it much better to change it so that the columns appear in a long table and add an extra column e.g. special_num. In your case: special_num tinyint UNSIGNED, start_date date, end_date date, special char(255), etc.. 1) This makes it easy to add more specials without doing an alter table. 2) You can get the same output as your original design by doing self joins: SELECT T1.special AS special_1, T2.special AS special_2, etc. FROM table T1, table T2 WHERE T1.special_num=1 AND T2.special_num=2 AND etc.. I will probably need to add up to 3 other fields to keep track of the last shown date. Add a field to the above table. one. What complicates this is that there are UP TO to 3 specials per record. Some of them may not include any specials at all. Some may have 1 or 2. Special 1 should be shown before Special 2. etc. Only 1 Special [snip] If you have a master table with specials, you can do a left join to get records where there are no specials. You can set up some sort of linkage between the main table an the specials, eg. an auto_increment field in the main table or perhaps a part number, etc.. --Bill Adams - 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: Query Question
[EMAIL PROTECTED] writes:
[SNIP]
Isn't it annoying when it refuses to send your mails to the list
and then lets through a ton of spam? Mysql mysql mysql. There. :-E
Carl Schrader writes:
select Title from inv where Title LIKE '%TOMMY%' OR Content LIKE
'%TOMMY%' OR Notes LIKE '%TOMMY%' order by Title
What I need is to have the results that match Title LIKE '%TOMMY%' to
appear first and then the rest.
ORDER BY !LOCATE('tommy',Title), Title should be enough
--
Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280
[EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/
Amiga user since '89, and damned proud of it too.
-
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 Question
I'm doing a query like: select Title from inv where Title LIKE '%TOMMY%' OR Content LIKE '%TOMMY%' OR Notes LIKE '%TOMMY%' order by Title What I need is to have the results that match Title LIKE '%TOMMY%' to appear first and then the rest. I have been doing 2 separate queries and filtering the first results from the second in a script. I would like to do this with a single query. Can that be done? - 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
explain query question
sql optimize Please help me. I can't understand why i've got this results.I have very simple table with name cz Field TypeNullKey Default Extra Privileges tv int(11) MUL 0 select,insert,update,references rez_id int(11) 0 select,insert,update,references it filled with tv rez_id 2 0 2 0 2 0 2 0 2 0 2 0 1 0 1 0 when i do: desc select rez_id from cz.rez_inc where tv=1 i got: table typepossible_keys key key_len ref rowsExtra rez_inc ref tv tv 4 const 2 where used but,when i do: desc select rez_id from cz.rez_inc where tv=2 table typepossible_keys key key_len ref rowsExtra rez_inc ALL tv NULLNULLNULL8 where used is it right? (i mean diffrent TYPE ,key ,ley_len) may be you can advise some articles about mysql optimisation? thanks. -- ___ Talk More, Pay Less with Net2Phone Direct(R), up to 1500 minutes free! http://www.net2phone.com/cgi-bin/link.cgi?143 - 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
DBASE query question.
hai In mysql it can be done but do anyone know how to join table in DBASE? The situation :- table 1 - EM_id EM_gender table 2 - EN_id EN_name The question is how to select all field from table1 and EN_name from table2 at one query? .The EM_id and EN_id is same.Is that any idea. please help me. thanks in advance.
Query Question
Hi, Can any of you query experts tell me if this is possible in a query? I have 3 tables, categories, products, groups. Groups table consists of categoryid and productid, it just keeps track of which categories have which products. I need a query(if possible) that would basically say: Select all products that do not have an entry in groups with the corresponding categoryid of 1000 (example id) Basically, I bring up the category on a page, with all of the products that are grouped with it, and I want to select all the other products that are not already there. CategoryProductsGroups --- IDIDCID |PID --- 10001 10001 2 10002 3 4 Query would return products 3 4 because 1 2 are already grouped with Category 1000 Make sense? Chris
left outer join query question
Hi, I am having a small formatting problem maybe someone can help with. I have a query that does a left outer join, which generates a table with a few NULL values in some columns. All okay so far. But what I need to do is sort on this column, and I need to put NULL values at the end. Normally I would just set all NULL values to be some number like 100, but since they do not exist before the join I cannot do this. Is there a way to assign a value in the query? Like if col2=NULL col2=100? Or maybe some other nice little trick? I looked in the manual, the Paul DuBois book, and usenet. Didn't find anything useful. Thanks! What I have: col1col2 a 1 b 3 c NULL d 2 e NULL f 4 What I want: col1col2 a 1 d 2 b 3 f 4 c NULL e NULL What I get: col1col2 c NULL e NULL a 1 d 2 b 3 f 4 - 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: left outer join query question
You could try using the IFNULL() function on that column before you do the join. http://www.mysql.com/doc/C/o/Control_flow_functions.html --jfarr - Original Message - From: Bryan Coon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 18, 2001 11:12 AM Subject: left outer join query question Hi, I am having a small formatting problem maybe someone can help with. I have a query that does a left outer join, which generates a table with a few NULL values in some columns. All okay so far. But what I need to do is sort on this column, and I need to put NULL values at the end. Normally I would just set all NULL values to be some number like 100, but since they do not exist before the join I cannot do this. Is there a way to assign a value in the query? Like if col2=NULL col2=100? Or maybe some other nice little trick? I looked in the manual, the Paul DuBois book, and usenet. Didn't find anything useful. Thanks! What I have: col1 col2 a 1 b 3 c NULL d 2 e NULL f 4 What I want: col1 col2 a 1 d 2 b 3 f 4 c NULL e NULL What I get: col1 col2 c NULL e NULL a 1 d 2 b 3 f 4 - 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
select query question
I have a table where patient visits are logged each visit, the table is: patientnumber,visitdate,location I need to select and count the number of records that have 2 or more entries with the same patientnumber in sql-english: select count(*) where there are two or more records with the same patientnumber Any help with the SQL to do this would be much appreciated. Thanks! Jon - 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: Query Question
Hi, Since you have over 10,000 member ids it depends on the exact appln that you have. If the length of the SQL query increases above a certain length (i am not sure abt the exact figure), an error is thrown saying query too complex. such wont be the case in your former method. but, if there are n member ids to be updated, the former update query will have to be executed n number of times, giving n disk accesses from your VB program, while the later query would need just 1. So if you can guarentee that the query string will not become too long, then the later update statement is good. If you are not sure, its better to go for the former update stmt and forget abt disk write time(at least ur program wont crash). -Sagar __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ - 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
Empty Set Query question
Hello everyone. I am new to this list and hope it's the right one for my question. This may be really obvious, but I can't seem to figure it out...so here's the problem: I have a table that stores network incident report information, IP address, dates, type of incident, report name, etc. I am trying to query the table for a specific ip address that's in the source_ip field. I can see that the record is there when I query all of the records, but when I query for one specific ip, I get the empty set result! :( Here is my query select * from IDReport where source_ip=xxx.xxx.xxx.xxx; The field type is char(16). Any ideas why mysql is not finding that record?? I appreciate ANY help on this matter!!! :) Thanks, Erica -- Erica B. Tanner Naval Surface Warfare Center, Dahlgren Division Advanced Computation Technology Group [EMAIL PROTECTED] 540.653.5796 - 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: Empty Set Query question
You are doing a text compare, and they have to match exactly, including leading zeros after the dots. You might look at storing them as integers. MySQL has to functions INET_ATON() and INET_NTOA() to convert them back and forth to strings. Erica B. Tanner wrote: Hello everyone. I am new to this list and hope it's the right one for my question. This may be really obvious, but I can't seem to figure it out...so here's the problem: I have a table that stores network incident report information, IP address, dates, type of incident, report name, etc. I am trying to query the table for a specific ip address that's in the source_ip field. I can see that the record is there when I query all of the records, but when I query for one specific ip, I get the empty set result! :( Here is my query select * from IDReport where source_ip=xxx.xxx.xxx.xxx; The field type is char(16). Any ideas why mysql is not finding that record?? I appreciate ANY help on this matter!!! :) Thanks, Erica -- Erica B. Tanner Naval Surface Warfare Center, Dahlgren Division Advanced Computation Technology Group [EMAIL PROTECTED] 540.653.5796 - 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 -- Gerald L. Clark [EMAIL PROTECTED] - 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
Newbie group/count query question
I have a table which contains a date column and an order_number column. I need to formulate a query syntax to return the total number of orders for each day in a given month (if any). Can someone help me with the syntax please as I've been stumbling around with it all day without success. Many thanks, Graham - 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: Empty Set Query question
Thanks to all for the suggestions!! Turns out, if I use a regular expression instead of a text compare I get the desired result!! :) Thanks! Erica Gerald Clark wrote: You are doing a text compare, and they have to match exactly, including leading zeros after the dots. You might look at storing them as integers. MySQL has to functions INET_ATON() and INET_NTOA() to convert them back and forth to strings. Erica B. Tanner wrote: Hello everyone. I am new to this list and hope it's the right one for my question. This may be really obvious, but I can't seem to figure it out...so here's the problem: I have a table that stores network incident report information, IP address, dates, type of incident, report name, etc. I am trying to query the table for a specific ip address that's in the source_ip field. I can see that the record is there when I query all of the records, but when I query for one specific ip, I get the empty set result! :( Here is my query select * from IDReport where source_ip=xxx.xxx.xxx.xxx; The field type is char(16). Any ideas why mysql is not finding that record?? I appreciate ANY help on this matter!!! :) Thanks, Erica -- Erica B. Tanner Naval Surface Warfare Center, Dahlgren Division Advanced Computation Technology Group [EMAIL PROTECTED] 540.653.5796 - 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 -- Gerald L. Clark [EMAIL PROTECTED] -- Erica B. Tanner Naval Surface Warfare Center, Dahlgren Division Advanced Computation Technology Group [EMAIL PROTECTED] 540.653.5796 - 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: Newbie group/count query question
hi. your table looks something like this: table1 - date(date) order_number int(6) ...i wasn't sure if order_number is referring to a order table somewhere... if there are multiple rows for each day, and you want to add up order_number for each day, use: select DAYOFMONTH(date) as d, SUM(order_number) from table1 group by d if you want to count the rows per day (which i think is what you're after), use: select DAYOFMONTH(date) as d, count(order_number) from table1 group by d you probably want to add in a where clause where MONTH(date) = 4 to limit the rows to all days in april, for example. hth. -ravi. -Original Message- From: Graham Nichols [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 11:23 AM To: [EMAIL PROTECTED] Subject: Newbie group/count query question I have a table which contains a date column and an order_number column. I need to formulate a query syntax to return the total number of orders for each day in a given month (if any). Can someone help me with the syntax please as I've been stumbling around with it all day without success. Many thanks, Graham - 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: Newbie group/count query question
On Tue, 1 May 2001, Graham Nichols wrote: I have a table which contains a date column and an order_number column. I need to formulate a query syntax to return the total number of orders for each day in a given month (if any). Can someone help me with the syntax please as I've been stumbling around with it all day without success. Many thanks, Graham select data_column,count(*) as number_of_orders from my_table where MONTH(date_column)=# group by TO_DAYS(date_column); and you replace the # with the month number you are looking for regards, thalis - 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 Question
I have a members table which stores, among other things, a Member ID and a Points field. We get reports sent to us in a CSV format, with a list of all Members signing up for a certain offer. Basically, what I need to do, is create a query like this: UPDATE members SET points = points + (offer_value) WHERE member_id = (member_id) for each member ID. With a small VB program I could create a file that has these queries (upwards of 10,000), but I've heard something about a query like this: UPDATE members SET points = points + (offer_value) WHERE member_id IN (7,8,9,10,23,etc) Just wondering which format would be better for the server, assuming there are upwards of 10,000 member_ids, whether it be one of these solutions or another one. TIA, Daren Cotter - 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
SQL query question?
Hi there, So a question I have two tables. And i want to... (i just show you) Table 1. OwnerChar(30)Uniqe FileChar(80) OIDint(not in use yet) Table 2. IDintuniqe and so on OwnerChar NameChar adress etc So I want to give the OID in table 1 the corresponding ID from Table 2 so that I may remove The Owner Col. I can do it with using tmp tables and so on, ( my knowlage in SQL querys is wery limited), But I want to do it with Just ONE singel SQL line Any ideas? /roger
Re: SQL query question?
It's not possible to do in one step in MySQL. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.com/ "roger westin" [EMAIL PROTECTED] wrote: So a question I have two tables. And i want to... (i just show you) Table 1. OwnerChar(30)Uniqe FileChar(80) OIDint(not in use yet) Table 2. IDintuniqe and so on OwnerChar NameChar adress etc So I want to give the OID in table 1 the corresponding ID from Table 2 so that I may remove The Owner Col. I can do it with using tmp tables and so on, ( my knowlage in SQL querys is wery limited), But I want to do it with Just ONE singel SQL line Any ideas? /roger - 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 Question
I have a query question I can't figure out on my own. Any help is appreciated... I have three tables: create table courses ( rec_num int(5) not null default '0' auto_increment, name varchar(255), description varchar(255), class_time varchar(255), professor_id int(5), units float(4,2), location varchar(255), quarter varchar(255), department_id int (5), primary key (rec_num) ); create table enroll_course ( rec_num int(5) not null default '0' auto_increment, course_id int(5), student_id int(5), grade varchar(10), primary key(rec_num) ); create table course_prereq ( rec_num int(5) not null default '0' auto_increment, source_course_id int(5), pre_req_course_id int(5), primary key (rec_num) ); Basically I want a query that looks at courses, sees if there are any pre-reqs, if so, checks enroll_course to see if they've been met, and if not, return a list of the courses that have not been met. Is this even possible with one query? Hunter - 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 Question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Excuse the wrapping.. Any ideas on how to speed this up? takes 10 secs on a p2 300. If i could get the second table to not scan all 132,775 rows, it would be great. I have indexes in it: mysql show keys from suppliersiclink; +-++-+--+- - +---+-+--+ | Table | Non_unique | Key_name| Seq_in_index | | Column_name | Collation | Cardinality | Sub_part | +-++-+--+- - +---+-+--+ | suppliersiclink | 1 | sic_link|1 | id | | A |NULL | NULL | suppliersiclink | | 1 | sic_index |1 | sic_code| A | | NULL | NULL | suppliersiclink | 1 | supplier_id | | 1 | sup_id | A |NULL | NULL | +-++-+--+- - +---+-+--+ Also, this query does not work in mySQL 3.23.33 any idea why? mysql explain - SELECT supplier.id,supplier.company_name,supplier.contact_name,supplier.addre ss_street, - supplier.address_city,supplier.address_state,supplier.address_zip,supp lier.phone_business, - supplier.url,supplier.miniweb_live,supplier.miniweb_name,supplier.spec ial_live, - subcatsicbond.sub_catid,subcatsicbond.siccode,specials.id,specials.s_t itle - FROM subcatsicbond - LEFT JOIN suppliersiclink ON subcatsicbond.siccode=suppliersiclink.sic_code - LEFT JOIN supplier ON suppliersiclink.sup_id=supplier.id - LEFT JOIN specials ON supplier.id=specials.sup_id - WHERE ((subcatsicbond.sub_catid=20) AND (supplier.max_latitude=32.99) - AND (supplier.min_latitude=31.55) AND (supplier.min_longitude=89.25) - AND (supplier.max_longitude=90.97)) - ORDER BY supplier.company_name ASC LIMIT 0,10; +-+--+---+-+-+ - +++ | table | type | possible_keys | key | key_len | | ref| rows | Extra | +-+--+---+-+-+ - +++ | subcatsicbond | ref | supplier_id | supplier_id | 4 | | ???| 7 || suppliersiclink | | ALL | sic_index | NULL|NULL | NULL | | 132775 || supplier| ref | vendor_key| | vendor_key | 4 | suppliersiclink.sup_id | 20 | where | used | specials| ALL | supplier_key | NULL| | NULL | NULL | 2 || +-+--+---+-+-+ - +++ 4 rows in set (0.00 sec) - - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.954.9133 -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.5.3 for non-commercial use http://www.pgp.com iQA/AwUBOpP9vbFNxPoD98ryEQJPkwCgjIQ7hlHAk17sAQfSW38w0PkAEaoAoLIk AczTsf21QrUIwPfxiKstSf2Z =Kwl9 -END PGP SIGNATURE- - 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
