Re: SELECT Help
Hi Travis, That query kind of gives me the desired result. However, if is showing me 1, 18, 11, 23, 3, 2010-11-14 17:18:17 record and not 2, 11, 10, 3, 6, 2010-12-20 22:17:13, which is when they changed teams. Any thoughts ? Cheers Neil On Thu, Feb 3, 2011 at 10:32 PM, Travis Ard travis_...@hotmail.com wrote: Something like this might help you find all of the times where your user_id switched to a different team_id: select team_id, user_id, min(last_changed) from (select home_team_id as team_id, home_user_id as user_id, last_changed from data union all select away_team_id as team_id, away_user_id as user_id, last_changed from data) s1 where s1.user_id = 3 group by team_id, user_id; -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, February 03, 2011 6:34 AM To: [MySQL] Subject: SELECT Help Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18:37:44 1, 20, 11, 69, 3, 2010-11-17 23:07:49 1, 1, 11, 4, 3, 2010-11-18 19:00:26 1, 11, 1, 3, 4, 2010-11-18 19:00:42 1, 12, 11, 5, 3, 2010-11-19 22:49:49 1, 11, 14, 3, 19, 2010-11-23 21:38:19 1, 3, 11, 15, 3, 2010-11-25 22:08:23 1, 7, 11, 66, 3, 2010-11-28 02:38:15 2, 73, 60, 137, 3, 2010-12-08 00:22:30 2, 60, 73, 3, 137, 2010-12-08 00:22:35 2, 60, 37, 3, 112, 2010-12-09 20:05:44 2, 60, 65, 3, 158, 2010-12-12 21:45:14 2, 72, 60, 141, 3, 2010-12-13 15:38:25 2, 60, 68, 3, 87, 2010-12-13 16:08:08 2, 60, 45, 3, 8, 2010-12-13 22:34:40 2, 66, 60, 140, 3, 2010-12-14 22:10:42 2, 60, 71, 3, 142, 2010-12-16 19:48:46 2, 60, 64, 3, 30, 2010-12-19 16:41:21 2, 76, 60, 17, 3, 2010-12-19 19:17:04 2, 60, 76, 3, 17, 2010-12-20 00:40:56 *2, 11, 10, 3, 6, 2010-12-20 22:17:13* 2, 13, 11, 104, 3, 2010-12-21 00:36:37 2, 6, 11, 168, 3, 2010-12-29 20:20:52 2, 11, 18, 3, 97, 2010-12-29 20:41:07 2, 20, 11, 5, 3, 2010-12-30 21:24:58 2, 15, 11, 163, 3, 2010-12-30 21:46:39 2, 13, 11, 12, 3, 2010-12-30 22:33:15 Basically, I need to find the point in which the user for either home_user_id or away_user_id (in this instance 3) changed teams for home_team_id or away_team_id - if you understand what I mean ? Any ideas on how I can achieve this using MySQL ? Cheers Neil
RE: SELECT Help
Something like this might help you find all of the times where your user_id switched to a different team_id: select team_id, user_id, min(last_changed) from (select home_team_id as team_id, home_user_id as user_id, last_changed from data union all select away_team_id as team_id, away_user_id as user_id, last_changed from data) s1 where s1.user_id = 3 group by team_id, user_id; -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, February 03, 2011 6:34 AM To: [MySQL] Subject: SELECT Help Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18:37:44 1, 20, 11, 69, 3, 2010-11-17 23:07:49 1, 1, 11, 4, 3, 2010-11-18 19:00:26 1, 11, 1, 3, 4, 2010-11-18 19:00:42 1, 12, 11, 5, 3, 2010-11-19 22:49:49 1, 11, 14, 3, 19, 2010-11-23 21:38:19 1, 3, 11, 15, 3, 2010-11-25 22:08:23 1, 7, 11, 66, 3, 2010-11-28 02:38:15 2, 73, 60, 137, 3, 2010-12-08 00:22:30 2, 60, 73, 3, 137, 2010-12-08 00:22:35 2, 60, 37, 3, 112, 2010-12-09 20:05:44 2, 60, 65, 3, 158, 2010-12-12 21:45:14 2, 72, 60, 141, 3, 2010-12-13 15:38:25 2, 60, 68, 3, 87, 2010-12-13 16:08:08 2, 60, 45, 3, 8, 2010-12-13 22:34:40 2, 66, 60, 140, 3, 2010-12-14 22:10:42 2, 60, 71, 3, 142, 2010-12-16 19:48:46 2, 60, 64, 3, 30, 2010-12-19 16:41:21 2, 76, 60, 17, 3, 2010-12-19 19:17:04 2, 60, 76, 3, 17, 2010-12-20 00:40:56 *2, 11, 10, 3, 6, 2010-12-20 22:17:13* 2, 13, 11, 104, 3, 2010-12-21 00:36:37 2, 6, 11, 168, 3, 2010-12-29 20:20:52 2, 11, 18, 3, 97, 2010-12-29 20:41:07 2, 20, 11, 5, 3, 2010-12-30 21:24:58 2, 15, 11, 163, 3, 2010-12-30 21:46:39 2, 13, 11, 12, 3, 2010-12-30 22:33:15 Basically, I need to find the point in which the user for either home_user_id or away_user_id (in this instance 3) changed teams for home_team_id or away_team_id - if you understand what I mean ? Any ideas on how I can achieve this using MySQL ? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT help.
Thank you very much to all who responded. I ended up using Shawn's solution, the others seem good as well. Thanks again. Have a great weekend. Richard [EMAIL PROTECTED] wrote: Try this: SELECT c_no , SUM(1) as total_tx , SUM(if(`date` = now() - interval 6 month,1,0)) as recent_tx FROM transactions_table GROUP BY c_no HAVING total_tx 4 and recent_tx = 0; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 01/05/2006 10:43:15 AM: - Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. What version of MySQL are you using? I would give a different answer if you had a version that supported subqueries than if you were using an older version that didn't support them. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
Re: SELECT help.
- Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. What version of MySQL are you using? I would give a different answer if you had a version that supported subqueries than if you were using an older version that didn't support them. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help.
3.23.54 Thanks. Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Richard Reina To: Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. What version of MySQL are you using? I would give a different answer if you had a version that supported subqueries than if you were using an older version that didn't support them. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
Re: SELECT help.
Try this: SELECT c_no , SUM(1) as total_tx , SUM(if(`date` = now() - interval 6 month,1,0)) as recent_tx FROM transactions_table GROUP BY c_no HAVING total_tx 4 and recent_tx = 0; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 01/05/2006 10:43:15 AM: - Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. What version of MySQL are you using? I would give a different answer if you had a version that supported subqueries than if you were using an older version that didn't support them. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help.
Richard Reina wrote: Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? transactions_table | ID | C_NO |DATE | AMOUT | | 2901 | 387 | 2003-10-09 | 23.00 | Obviously my table has many more entries. Thank you for any help. Sincerely, Richard Reina A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower. Something like: SELECT C_NO FROM transactions_table GROUP BY C_NO HAVING COUNT(*) = 4 AND COUNT(DATE CURDATE() - INTERVAL 6 MONTH) = 0; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help.
This should work: select c_name, count(t1.id) as t_count from customers c inner join transactions t1 on c.c_no = t1.c_no left join transactions t2 on c.c_no = t2.c_no and t2.date '2005-06-05' where t2.id is null group by c.c_no having t_count 4; There may be more efficient way of doing this though, if your tables are very large. This might work too: select c_name, count(t.id) as t_count, max(t.date) as t_latest from customers c inner join transactions t on c.c_no = t.c_no group by c.c_no having t_count 4 and t_latest '2005-06-05'; HTH, James Harvard At 7:29 am -0800 5/1/06, Richard Reina wrote: Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table| | ID|C_NO|DATE | AMOUT| |2901 | 387|2003-10-09 | 23.00| Obviously my table has many more entries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help.
Richard, Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? Something like this? SELECT c_no, COUNT(c_no) AS cnt FROM transactions_table WHERE NOT EXISTS ( SELECT c_no FROM transaction_table WHERE DATEDIFF(NOW(),date)=182 ) GROUP BY c_no HAVING cnt 4; PB - Richard Reina wrote: Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|"2003-10-09"|23.00| Obviously my table has many more entries. Thank you for any help. Sincerely, Richard Reina A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 1/4/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 1/4/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help
You want a LEFT JOIN: SELECT table1.title, table2.feature FROM table1 LEFT JOIN table2 USING (sku) WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC I strongly suggest picking up Paul DuBois' MySQL: http://www.kitebird.com/mysql-book/ Eamon Daly - Original Message - From: Grant Giddens [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 28, 2005 6:00 PM Subject: SELECT help Hi, I am tring to do a select from 2 tables. Table1: sku title Table 2: sku feature SELECT table1.title, table2.feature FROM table1, table2 WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seems to work to some extint, but I am getting way too many results (more than 3). It's returning all combinations of sku and feauture even if they don't share the same sku. I modified the select to: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC That seemed to work almost correctly. I have some items in table2 that don't have a feature and therefor don't have a row associated with them. For example, if I have 3 items in each table, the above select works fine. If I have 3 items in table1 and 2 items in table2 the above query only gives me 2 results. table1 will always be fully populated and table2 might be missing some features. How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? Thanks, Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help
Gran Giddens writes: SELECT table1.title, table2.feature FROM table1, table2 WHERE (table1.sku = $table2.sku) AND table1.sku in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC ... How can I run my query to get 3 results and if the feature is missing still return the table.title and NULL for the feature? This is a job for 'LEFT JOIN' :) Given this data from your described tables: mysql select * from table1; +--+---+ | sku | title | +--+---+ |1 | A | |2 | B | |3 | C | +--+---+ 3 rows in set (0.00 sec) mysql select * from table2; +--+-+ | sku | feature | +--+-+ |1 | a | |1 | aa | |2 | b | |2 | bb | |2 | bbb | +--+-+ 5 rows in set (0.00 sec) SELECT table1.title, table2.feature FROM table1 LEFT JOIN table2 using (sku) WHERE table1.sku in (1, 2, 3) ORDER BY FIELD(table1.sku, 1, 2, 3) ASC mysql SELECT table1.title, table2.feature - FROM table1 LEFT JOIN table2 using (sku) - WHERE table1.sku in (1, 2, 3) - ORDER BY FIELD(table1.sku, 1, 2, 3) ASC - ; +---+-+ | title | feature | +---+-+ | A | a | | A | aa | | B | bbb | | B | b | | B | bb | | C | NULL| +---+-+ 6 rows in set (0.04 sec) Take a look at the manual for 'LEFT JOIN' to see where I came up with this information. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select help
Hey Rob, You're looking for a group by to allow mysql to aggregate over the IP's: SELECT ip, count(*) FROM iptable GROUP BY ip ORDER BY ip DESC limit 10; -Matt -Original Message- From: rmck [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:03 PM To: [EMAIL PROTECTED] Subject: Select help Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
select count(*) as cnt group by ip order by cnt desc limit 10; rmck wrote: Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select help
Woops! Forget I said that, you wanted to order by the most occurrences. Sorry. SELECT ip, count(*) FROM iptable GROUP BY ip ORDER BY 2 DESC limit 10; Heh... I should learn to read one of these days... -Matt -Original Message- From: rmck [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 1:03 PM To: [EMAIL PROTECTED] Subject: Select help Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
On Thu, 2004-07-01 at 10:03, rmck wrote: Hi, I have a table with ip,port and I want to see the top ten Ip's with the most entries? Ip's can be in db many times... Not the first distinct 10... Im stuck... I have tried: mysql select DISTINCT ip from iptable limit 10; +---+ | ip | +---+ | 0.0.0.0 | | 10.0.1.42 | | 10.0.1.8 | | 10.1.1.1 | | 10.10.10.1| | 10.115.94.193 | | 10.115.94.195 | | 10.115.94.40 | | 10.122.1.1| | 10.20.7.184 | +---+ 10 rows in set (0.04 sec) mysql But doesn't that just give the first 10 DISTINCT ip's?? Yes. You need to count the number of times an IP appears and sort by that count, then limit it: SELECT ip, COUNT(ip) as num FROM iptable GROUP BY ip ORDER BY num DESC LIMIT 10 -- . Garth Webb . [EMAIL PROTECTED] . . shoes * * schoenen * * chaussures * zapatos . Schuhe * * pattini * * sapatas * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Andre, have a look at JOIN. This can solve your problem. Thomas Spahni On Fri, 30 Apr 2004, Andre MATOS wrote: Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Is this possible? Is there anyone who can help me to create this SELETC? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Andre MATOS wrote: Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Not knowing what the criteria for selecting the different records from table_A (1 and 15), I'll forgo a join clause and just illustrate a simple alias with where clause: SELECT (( a1.value + a2.value ) / b.value ) + c.value AS final_result FROM table_A a1, table_A a2, table_B b, table_C c WHERE a1.key = 1 AND a2.key = 15 AND /* guessing here */ b.key = a1.key AND c.key = a2.key AND a1.key a2.key AND a2.key IS NOT NULL AND a1.key IS NOT NULL; That's not correct as I am guessing your actual criteria, etc., but it gives the idea. Can you be more specific on the criteria for relating (joining) tables A, records 1 and 15, with themselves and with tables B and C? Is this possible? Is there anyone who can help me to create this SELETC? Thanks. HTH, Robert Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Hi Robert, the criteria for the record_1 and record_15 is that both are in the same table, but in different records and to find each one it is necessary to perform a WHERE clause. Let's I give you the real example: My problem is while inserting a new record in my table named ScanQuantification, I will need to update another table named Scan, IF a value from the field ScanQuantificationValue from table ScanQuantification is equal or greater than a calculate value. The calculate value comes from this formula: ( ( A + B ) / 2 + C) where: A is a value find from the field ScanQuantificationValue from table ScanQuantification where the TimePoint = 8 B is a value find from the field ScanQuantificationValue from table ScanQuantification where the TimePoint = 9 C is a value find from the field TrialBaseValue from table Trial This is easy to do using the PHP language. However I will have different Trials and each one has different formula. That's why I want to put the SELECT to work for me. Thanks Andre On Fri, 30 Apr 2004, Robert J Taylor wrote: Andre MATOS wrote: Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Not knowing what the criteria for selecting the different records from table_A (1 and 15), I'll forgo a join clause and just illustrate a simple alias with where clause: SELECT (( a1.value + a2.value ) / b.value ) + c.value AS final_result FROM table_A a1, table_A a2, table_B b, table_C c WHERE a1.key = 1 AND a2.key = 15 AND /* guessing here */ b.key = a1.key AND c.key = a2.key AND a1.key a2.key AND a2.key IS NOT NULL AND a1.key IS NOT NULL; That's not correct as I am guessing your actual criteria, etc., but it gives the idea. Can you be more specific on the criteria for relating (joining) tables A, records 1 and 15, with themselves and with tables B and C? Is this possible? Is there anyone who can help me to create this SELETC? Thanks. HTH, Robert Taylor [EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select help - multiple where/limits
Kris Burford [EMAIL PROTECTED] wrote: hi wondering whether someone can set me straight on whether it's possible to request a set of records from a single table with multiple conditions. for instance, a story table, containing id, title, text, section and published_date. what i would like is to retrieve is the 5 most recently published stories from each section (currently there are nine sections). so, do i have to do this in nine separate queries or can i do something like: SELECT id, title, text, sectioned, published_date FROM stories WHERE (section = 'events' order by published_date desc limit 5) and (section = 'features' order by published_date desc limit 5) If I've got you right you need UNION: (SELECT id, title, text, sectioned, published_date FROM stories WHERE section = 'events' ORDER BY published_date DESC LIMIT 5) UNION (SELECT id, title, text, sectioned, published_date FROM stories WHERE section = 'features' ORDER BY published_date DESC LIMIT 5); http://www.mysql.com/doc/en/UNION.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
Hi, I want to select from the table sum of logins for each day. Would this help: mysql select date_format(your_date_column, %Y-%m-%d), count(*) - from your_table - group by date_format(your_date_column, %Y-%m-%d); Take care, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select help
- Original Message - From: Mike Mapsnac [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 23, 2004 11:42 PM Subject: Select help Hello I want to select from the table sum of logins for each day. Here's one way to do it. SELECT SUBSTRING(last_login, 1, 10) AS day, login_count FROM table GROUP BY day ORDER BY day ASC; For example: Date Logins 2004-01-22 10 2004-01-23 12 Any ideas if such select is possible? +--+--+ | Field| Type | +--+--+ | login_count | int(4) | | last_login | datetime | Mikael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT help
Try with : SELECT tparts.name, a.name, b.name, tmovement.quantity FROMtmovement inner join tparts on (tmovement.part_id = tparts.Id) inner join twarehouse a on ( tmovement.incoming_id = a.Id ) inner join twarehouse b on ( tmovement.outgoing_id = b.Id ) Ulises -Mensaje original- De: Gyurasits Zoltan [mailto:[EMAIL PROTECTED] Enviado el: Martes 18 de Febrero de 2003 04:11 PM Para: MYSQL Lista Asunto: SELECT help HI all! I want make the SELECT for this situation. Example: Twarehouse -- IDname --- 01Spring_WH 02Screw_WH 03Toll_WH Tparts -- IDname --- 01M3 screw 02M4 screw 03Hammer Tmovement -- part_ID incoming_IDoutgoing_ID quantity ---- - 01010210 0201031 0302015 I want build list about movement, with warehouse's names SELECT twarehouse.name, ??# incoming warehouse name twarehouse.name, ??# outgoing warehouse name tparts.name, tmovement.quantity FROM tparts, twarehouse INNER JOIN tmovement ON tmovement.part_ID=tparts.ID AND tmovement.incoming_ID=twarehouse.ID AND tmovement.outgoing_ID=twarehouse.ID Please help me, who can . How can I do?? I don't want make temorary table, if is possible Thank You!
Re: SELECT help
Hi This is working... Thank You Zoltan - Original Message - From: Cabanillas Dulanto, Ulises [EMAIL PROTECTED] To: MYSQL Lista [EMAIL PROTECTED] Sent: Friday, July 18, 2003 10:54 PM Subject: RE: SELECT help Try with : SELECT tparts.name, a.name, b.name, tmovement.quantity FROMtmovement inner join tparts on (tmovement.part_id = tparts.Id) inner join twarehouse a on ( tmovement.incoming_id = a.Id ) inner join twarehouse b on ( tmovement.outgoing_id = b.Id ) Ulises -Mensaje original- De: Gyurasits Zoltan [mailto:[EMAIL PROTECTED] Enviado el: Martes 18 de Febrero de 2003 04:11 PM Para: MYSQL Lista Asunto: SELECT help HI all! I want make the SELECT for this situation. Example: Twarehouse -- IDname --- 01Spring_WH 02Screw_WH 03Toll_WH Tparts -- IDname --- 01M3 screw 02M4 screw 03Hammer Tmovement -- -- -- part_ID incoming_IDoutgoing_ID quantity ---- - 01010210 0201031 0302015 I want build list about movement, with warehouse's names SELECT twarehouse.name, ??# incoming warehouse name twarehouse.name, ??# outgoing warehouse name tparts.name, tmovement.quantity FROM tparts, twarehouse INNER JOIN tmovement ON tmovement.part_ID=tparts.ID -- -- AND tmovement.incoming_ID=twarehouse.ID AND tmovement.outgoing_ID=twarehouse.ID Please help me, who can . How can I do?? I don't want make temorary table, if is possible Thank You! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select help
I'm new, too, so someone correct me if I'm wrong, but... if you make it an ENUM field in a table you can store it using the value in the selection, retrieve it as the same value, and still get all the advantages of numeric storage. Todd On Sunday, July 6, 2003, at 02:38 PM, Dan Cox wrote: Hello list, I'm really new to mysql and databases in general. I have a select form that contains a very long list of options, and what I want to do is store the selected item as a number instead of the items name in order to speed up searches. My problem comes when a search is done and I can't figure out how to return the query with the items name not the number. Any suggestions and help is greatly appreciated. Dan Cox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select help
Another option is to have another table with the item name and number using that as a lookup table. IMHO it all depends on the data if the select is static (or rarely changes) an enum would be best. If is changes a lot then I would use another table to store it in. -Michael I protect you, skin brother. -mriswith On Sun, 6 Jul 2003, Todd O'Bryan wrote: I'm new, too, so someone correct me if I'm wrong, but... if you make it an ENUM field in a table you can store it using the value in the selection, retrieve it as the same value, and still get all the advantages of numeric storage. Todd On Sunday, July 6, 2003, at 02:38 PM, Dan Cox wrote: Hello list, I'm really new to mysql and databases in general. I have a select form that contains a very long list of options, and what I want to do is store the selected item as a number instead of the items name in order to speed up searches. My problem comes when a search is done and I can't figure out how to return the query with the items name not the number. Any suggestions and help is greatly appreciated. Dan Cox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
No problem. Use mod(m,n). To get the records where the 8 bit is set, use and mod(os.os_id,8) = 0; mysql use test Database changed mysql create table t (i integer); Query OK, 0 rows affected (0.18 sec) mysql insert into t values (1); Query OK, 1 row affected (0.10 sec) mysql insert into t values (2); Query OK, 1 row affected (0.00 sec) rows omitted for brevity, values 3..7 inserted mysql insert into t values (8); Query OK, 1 row affected (0.00 sec) mysql select * from t where mod(i,2) = 0; +--+ | i| +--+ |2 | |4 | |6 | |8 | +--+ 4 rows in set (0.00 sec) mysql select * from t where mod(i,4) = 0; +--+ | i| +--+ |4 | |8 | +--+ 2 rows in set (0.00 sec) -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:34 AM To: Michael Shulman Cc: [EMAIL PROTECTED] Subject: RE: select help Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
John, Looks like I'm first with the wrong answer again. This time for sure. How about: AND os.os_id 8 = 8 Where 8 is the value that you're looking for. -ms -Original Message- From: Michael Shulman [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:38 AM To: 'John Hoskins' Cc: '[EMAIL PROTECTED]' Subject: RE: select help No problem. Use mod(m,n). To get the records where the 8 bit is set, use and mod(os.os_id,8) = 0; mysql use test Database changed mysql create table t (i integer); Query OK, 0 rows affected (0.18 sec) mysql insert into t values (1); Query OK, 1 row affected (0.10 sec) mysql insert into t values (2); Query OK, 1 row affected (0.00 sec) rows omitted for brevity, values 3..7 inserted mysql insert into t values (8); Query OK, 1 row affected (0.00 sec) mysql select * from t where mod(i,2) = 0; +--+ | i| +--+ |2 | |4 | |6 | |8 | +--+ 4 rows in set (0.00 sec) mysql select * from t where mod(i,4) = 0; +--+ | i| +--+ |4 | |8 | +--+ 2 rows in set (0.00 sec) -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:34 AM To: Michael Shulman Cc: [EMAIL PROTECTED] Subject: RE: select help Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
This one worked. Thank you. On Wed, 2 Apr 2003, Michael Shulman wrote: John, Looks like I'm first with the wrong answer again. This time for sure. How about: AND os.os_id 8 = 8 Where 8 is the value that you're looking for. -ms -Original Message- From: Michael Shulman [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:38 AM To: 'John Hoskins' Cc: '[EMAIL PROTECTED]' Subject: RE: select help No problem. Use mod(m,n). To get the records where the 8 bit is set, use and mod(os.os_id,8) = 0; mysql use test Database changed mysql create table t (i integer); Query OK, 0 rows affected (0.18 sec) mysql insert into t values (1); Query OK, 1 row affected (0.10 sec) mysql insert into t values (2); Query OK, 1 row affected (0.00 sec) rows omitted for brevity, values 3..7 inserted mysql insert into t values (8); Query OK, 1 row affected (0.00 sec) mysql select * from t where mod(i,2) = 0; +--+ | i| +--+ |2 | |4 | |6 | |8 | +--+ 4 rows in set (0.00 sec) mysql select * from t where mod(i,4) = 0; +--+ | i| +--+ |4 | |8 | +--+ 2 rows in set (0.00 sec) -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:34 AM To: Michael Shulman Cc: [EMAIL PROTECTED] Subject: RE: select help Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
If you want to be a bit more generic you could do something like this: # store the desired OS ID into a variable SELECT @desired_id := os_id FROM os_table WHERE os_name = win nt; # now find the solutions that match with the os_id SELECT o.os_id, o.os_name, s.os_code, s.solution FROM os_table o, solution_table s WHERE (o.os_id s.os_code) = @desired_id; +---+-+-+-+ | os_id | os_name | os_code | solution| +---+-+-+-+ | 8 | win nt | 24 | nt and 2000 dun fix | | 8 | win nt | 255 | no-pay contact CSRs | +---+-+-+-+ 2 rows in set (0.01 sec) -- Jeff Shapiro | Starlight Spectacular Ride Webmaster| June 21st,2003 at midnight www.starlightspectacular.org | Benefiting the Trails Open Space Coalition On 4/2/03 at 15:40, John Hoskins spoke thusly: This one worked. Thank you. On Wed, 2 Apr 2003, Michael Shulman wrote: John, Looks like I'm first with the wrong answer again. This time for sure. How about: AND os.os_id 8 = 8 Where 8 is the value that you're looking for. -ms -Original Message- From: Michael Shulman [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:38 AM To: 'John Hoskins' Cc: '[EMAIL PROTECTED]' Subject: RE: select help No problem. Use mod(m,n). To get the records where the 8 bit is set, use and mod(os.os_id,8) = 0; mysql use test Database changed mysql create table t (i integer); Query OK, 0 rows affected (0.18 sec) mysql insert into t values (1); Query OK, 1 row affected (0.10 sec) mysql insert into t values (2); Query OK, 1 row affected (0.00 sec) rows omitted for brevity, values 3..7 inserted mysql insert into t values (8); Query OK, 1 row affected (0.00 sec) mysql select * from t where mod(i,2) = 0; +--+ | i| +--+ |2 | |4 | |6 | |8 | +--+ 4 rows in set (0.00 sec) mysql select * from t where mod(i,4) = 0; +--+ | i| +--+ |4 | |8 | +--+ 2 rows in set (0.00 sec) -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 11:34 AM To: Michael Shulman Cc: [EMAIL PROTECTED] Subject: RE: select help Not quite that simple, Plese read the last of the original post. I need all solutions that have the 4th bit on, so 8,15,24,31...255 all have the 4th bit in combination with other bits. On Wed, 2 Apr 2003, Michael Shulman wrote: mysql select solution - from os_table os, solutions_table solutions - where os.os_id = solutions.os_code - and os.os_id = 8; -ms -Original Message- From: John Hoskins [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: select help Please consider the following two tables: mysql select * from os_table; +---+--+ | os_id | os_name | +---+--+ | 1 | mac os | | 2 | win 95 | | 4 | win 98 | | 8 | win nt | |16 | win 2000 | |32 | win me | |64 | xp home | | 128 | xp pro | +---+--+ mysql select * from solution_table; +-+-+ | os_code | solution| +-+-+ | 1 | mac fix | | 24 | nt and 2000 dun fix | | 255 | no-pay contact CSRs | +-+-+ What I'd like to do is select all solutions that applys to NT, os_id.os_table=8 so it should return solution 24 and 255. Since these two solutions have the NT os bit turned on. Thank You John H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select help
Jeff Shapiro wrote: If you want to be a bit more generic you could do something like this: # store the desired OS ID into a variable SELECT @desired_id := os_id FROM os_table WHERE os_name = win nt; # now find the solutions that match with the os_id SELECT o.os_id, o.os_name, s.os_code, s.solution FROM os_table o, solution_table s WHERE (o.os_id s.os_code) = @desired_id; Why not combine them into a single intriguing non-equijoin? SELECT * FROM os_table o, solution_table s WHERE (o.os_id s.os_code) = o.os_id AND o.os_name = win nt; Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select help
On 26-Feb-2003 John Hoskins wrote: Probably a simple query but, I need to find select a field with data that exists in one table but does not exist in a field in another table. example: table1.name table2.name --- bob john susan jane tom tom john bob jane john jane tom result should be = susan any help is appreciated look for 'LEFT JOIN' in the manual. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table 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
RE: SELECT HELP
I believe you could use CONCAT(firstname, ' ', lastname) LIKE '%$searchname%' if you leave the space out of the middle it probably won't work, and you might want to trim firstname lastname to make sure no leading or trailing spaces exist. Hope this helps, P -Original Message- From: *Himerus* [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 10:09 PM To: [EMAIL PROTECTED] Subject: SELECT HELP How do I use the select feature in this situation. I have a total of 5 fields that deal with names.. Three of the five are completely separate, and use one field for both first and last name. the last two fields are the first and last name of the person submitting the information.. When I do a select if someone searches for joe or smith it will pull up the info on all fields. but if someone searches for joe smith it will ONLY pull up the entries that match in the first three fields. it is imperative that I make this work. I can't change the layout of the table at this point, but I'm sure there's a way to combine two fields in the select and try to match two fields in the table to one field in the search form.. Here's a sample of the code.. (SELECT * FROM newsibsdatabase WHERE (firstname LIKE '%$searchname%' OR lastname LIKE '%$searchname%' OR adopteename LIKE '%$searchname%') now, can I add in a option that is something like this OR (firstname lastname LIKE '%$searchname$') how would this work.. Thanks in advance.. Jake. - 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: SELECT help
Hi, This is my first posting, although i've been signed up to the list for a while. My problem is this. Table A (5000 rows) ID, NAME, SCORE Table B (1000 rows) ID, NAME, SCORE I want all records from Table A and those from Table B where they match, for this i'm using a right join. However, there are rows in Table B which don't match any in Table A, but i need to include these as well. Any help would be appreciated. Ben. Sir, I haven't seen an answer to your question, so here's one way of getting what you want. What you want is basically the union of three groups of rows: the rows from A and B that match, the rows from A that don't match B, and the rows from B that don't match A. Your outer join returns the first two groups. The last group is returned by a difference query: the rows in B that don't match any of the rows in A (B - A). Since MySQL doesn't yet support UNION, you will have to load the result tables from both queries into another table, and then SELECT * from that table. I have a description of the standard difference query on my website; http:/users.starpower.net/rjhalljr, click on MySQL on the sidebar, click on SQL, and look for the difference query topic. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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: SELECT help
Sir, haven't you posted this before? It looks familiar. You can't apply an aggregate function to an entire table if the SELECT statement has a GROUP BY clause. The aggregate function will return totals for the groups, not for the entire table. Bob Hall Can someone help me combine this statement ... SELECT d.*, b.invoice_id FROM domain_info d LEFT JOIN billing_info b ON d.domain_id=b.domain_id WHERE billing_cycle = '12' OR billing_cycle = 'Z' OR billing_cycle = 'C' GROUP BY domain_name with this statement ... SELECT sum(ammount_due) FROM billing_info WHERE domain_id = $domain_id AND status = 0 The code is for billing software to manage the accounts of my server clients. In the first statement I am selecting information about each domain from the table domain_info that are billable for this billing cycle. Also in the first statement I am checking to see if any invoice_ids exist for the domain in the table billing_info. If there are no ids, the code knows to add a setup fee to the charge. If there are ids, then they domain has already been charges in the past and been billed for the one-time setup fee. The second statment gets the entire ammount owed from the table billing_info. Anytime a domain incurs a charge (positive) or pays an invoice (neagtive), a line is added with this in the ammount_due collumn. I can't seem to get an error free result ... can anyone offer suggestions on some working codes that gets both of these statments into one? Thanks! Nick - 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 Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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
