Re: SQL Syntax Help
On Fri, Jan 31, 2003 at 02:07:11PM -, Kevin Smith wrote: Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM (products AS p LEFT JOIN product_options AS po ON p.prod_id = po.prod_id) RIGHT JOIN (product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) ON po1.po_id = b.op_id) ON p.prod_id = b.prod_id GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity, bh.basket_id, p.options HAVING (((bh.basket_id)=4)); Try FROM (((basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) LEFT JOIN product_options AS po1 ON po1.po_id = b.op_id) LEFT JOIN products AS p ON p.prod_id = b.prod_id) LEFT JOIN product_options AS po ON p.prod_id = po.prod_id MySQL tends to be more finicky than Jet about how you group things. I haven't tried this, but I think it will avoid confusing the MySQL optimizer. Bob Hall - 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 Syntax Help
Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM (products AS p LEFT JOIN product_options AS po ON p.prod_id = po.prod_id) RIGHT JOIN (product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) ON po1.po_id = b.op_id) ON p.prod_id = b.prod_id GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity, bh.basket_id, p.options HAVING (((bh.basket_id)=4)); Here is the error message MySQL reports: You have an error in your SQL syntax near '(product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b O' at line 1 Any ideas to the correct syntax? Thanks, Kevin - 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: Thanks and SQL Syntax help
I don't know if i understood you very well, but here's a try.. mysql select * from Classes; ++-+ | ID | Name| ++-+ | 1 | XO-312 | | 2 | PA-211a | | 3 | XUL-001 | ++-+ 3 rows in set (0.00 sec) mysql select * from Workshops order by ClassID,Date; ++-++ | ID | ClassID | Date | ++-++ | 1 | 1 | 2002-05-15 | | 8 | 1 | 2002-09-22 | | 7 | 1 | 2002-10-29 | | 2 | 1 | 2003-02-20 | | 3 | 2 | 2002-05-15 | | 9 | 2 | 2003-01-01 | | 4 | 2 | 2003-02-17 | | 5 | 3 | 2002-05-15 | | 10 | 3 | 2002-12-16 | | 6 | 3 | 2003-01-01 | ++-++ 10 rows in set (0.00 sec) mysql select ClassID, MIN(Date) min, MAX(Date) max, Classes.Name - FROM Workshops LEFT JOIN Classes ON (ClassID=Classes.ID) - GROUP BY ClassID HAVING now() BETWEEN min and max; +-+++-+ | ClassID | min| max| Name| +-+++-+ | 1 | 2002-05-15 | 2003-02-20 | XO-312 | | 2 | 2002-05-15 | 2003-02-17 | PA-211a | +-+++-+ 2 rows in set (0.00 sec) Hope this helps... On Sat, 2003-01-11 at 16:25, Steve Lefevre wrote: First of, thanks to all who replied to my questions earlier! Now I have another problem. I have a table of Classes and Workshops. Each Class has a number of workshops. Each workshop has a date. I have a query that gives me the date range of a class - the min and max dates of its workshops. select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID GROUP BY ClassID; gives me: +-+++-+ | ClassID | MIN(Date) | MAX(Date) | Name| +-+++-+ | 56 | 2002-05-15 | 2002-12-29 | XO-312 | | 408 | 2002-05-15 | 2002-05-17 | PA-211a | | 600 | 2002-05-15 | 2002-05-16 | XUL-001 | +-+++-+ 3 rows in set (0.00 sec) Now I want to get *active* classes - WHERE Now() Between MIN(Date) and Max(Date) -- but I can't figure out where to put the friggin clause. I get errors all over the place. Can I use the between function with a group by function? select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID WHERE Now() BETWEEN MIN(Date) and MAX(Date) GROUP BY ClassID; What am I doing wrong? - 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 -- Diana Soares - 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
Thanks and SQL Syntax help
First of, thanks to all who replied to my questions earlier! Now I have another problem. I have a table of Classes and Workshops. Each Class has a number of workshops. Each workshop has a date. I have a query that gives me the date range of a class - the min and max dates of its workshops. select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID GROUP BY ClassID; gives me: +-+++-+ | ClassID | MIN(Date) | MAX(Date) | Name| +-+++-+ | 56 | 2002-05-15 | 2002-12-29 | XO-312 | | 408 | 2002-05-15 | 2002-05-17 | PA-211a | | 600 | 2002-05-15 | 2002-05-16 | XUL-001 | +-+++-+ 3 rows in set (0.00 sec) Now I want to get *active* classes - WHERE Now() Between MIN(Date) and Max(Date) -- but I can't figure out where to put the friggin clause. I get errors all over the place. Can I use the between function with a group by function? select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID WHERE Now() BETWEEN MIN(Date) and MAX(Date) GROUP BY ClassID; What am I doing wrong? - 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 syntax help
You almost got it. Your syntax will be something like this: UPDATE Table SET address=REPLACE(address,'#','Number') WHERE column like%#% When I am trying to figure out the syntax for something, I always add a LIMIT 1 at the end so that only one record gets changed. On Saturday, October 5, 2002, at 12:45 AM, Scott Johnson wrote: I have a db with slightly over 614,000 records of names and addresses. In the address column, there are quite a few records like 123 any rd # 2 319 w. 1st st # B 4321 test blvd # 42 etc I want to replace all the number signs with the actual word 'number'. Is there a SQL command I can use for this or do I need the help of a scripting language (php or vb)? I was trying to construct something like update into table.column select where column like '%#%' replace with '%number%' but of course that is not going to work. I am a SQL newb btw. Any help appreciated. Thanks Scott - 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 mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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 syntax help
Scott, Saturday, October 05, 2002, 7:45:16 AM, you wrote: SJ I have a db with slightly over 614,000 records of names and addresses. In SJ the address column, there are quite a few records like SJ 123 any rd # 2 SJ 319 w. 1st st # B SJ 4321 test blvd # 42 SJ etc SJ I want to replace all the number signs with the actual word 'number'. SJ Is there a SQL command I can use for this or do I need the help of a SJ scripting language (php or vb)? SJ I was trying to construct something like update into table.column select SJ where column like '%#%' replace with '%number%' SJ but of course that is not going to work. I am a SQL newb btw. Take a look at string function REPLACE(): http://www.mysql.com/doc/en/String_functions.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 - 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 syntax help
I have a db with slightly over 614,000 records of names and addresses. In the address column, there are quite a few records like 123 any rd # 2 319 w. 1st st # B 4321 test blvd # 42 etc I want to replace all the number signs with the actual word 'number'. Is there a SQL command I can use for this or do I need the help of a scripting language (php or vb)? I was trying to construct something like update into table.column select where column like '%#%' replace with '%number%' but of course that is not going to work. I am a SQL newb btw. Any help appreciated. Thanks Scott - 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: Conditional join SQL syntax help?
Hi. On Mon 2002-07-08 at 22:54:09 -0700, [EMAIL PROTECTED] wrote: I'm trying to get the name of a booth or tradeshow depending on the customer_link_type (which is an ENUM) combined with the customer_link_table_id which tells me the index/id of the correct table to look in. I've tried this SQL command, but it doesn't work right. I get multiple permutations still. Is this even possible to do with mySQL? Or do I have to make two queries (one for 'booth' and one for 'tradeshow' and store them in a PHP array or something) In MySQL 4.x you can use UNION to accomplish what you want, in earlier versions you have to do two queries and do some application-side work or if you want the ORDER BY done by the database, you have to use a TEMPORARY TABLE. I think the problem could be avoided if the database design would be normalized further. Considering your select, it could be that a intermediate table, containing reference to type, name and id (and maybe others) would help and the both and tradeshow tables would only contain the information unique to them. Greetings, Benjamin. SELECT customer_id, customer_name, customer_link_type, customer_link_table_id, tradeshow_name, booth_name FROM Customer_Table, TradeShow_Table, Booth_Table WHERE ((customer_link_table_id = tradeshow_id AND customer_link_type = 'tradeshow') OR (customer_link_table_id = booth_id AND customer_link_type = 'booth')) AND customer_mail_list = 1 ORDER BY customer_date DESC, customer_link_type Here are the three relevant tables and fields (some removed for space saving): CREATE TABLE Customer_Table ( customer_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment, customer_link_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL, customer_link_type enum(booth, tradeshow) NOT NULL, customer_name varchar(30), customer_mail_list tinyint(1) UNSIGNED DEFAULT '0', ); CREATE TABLE TradeShow_Table ( tradeshow_id int(10) unsigned NOT NULL auto_increment, tradeshow_name varchar(100) NOT NULL default '' ) CREATE TABLE Booth_Table ( booth_id int(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment, booth_name varchar(30) NOT NULL ); -- [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
Conditional join SQL syntax help?
I'm trying to get the name of a booth or tradeshow depending on the customer_link_type (which is an ENUM) combined with the customer_link_table_id which tells me the index/id of the correct table to look in. I've tried this SQL command, but it doesn't work right. I get multiple permutations still. Is this even possible to do with mySQL? Or do I have to make two queries (one for 'booth' and one for 'tradeshow' and store them in a PHP array or something) SELECT customer_id, customer_name, customer_link_type, customer_link_table_id, tradeshow_name, booth_name FROM Customer_Table, TradeShow_Table, Booth_Table WHERE ((customer_link_table_id = tradeshow_id AND customer_link_type = 'tradeshow') OR (customer_link_table_id = booth_id AND customer_link_type = 'booth')) AND customer_mail_list = 1 ORDER BY customer_date DESC, customer_link_type Here are the three relevant tables and fields (some removed for space saving): CREATE TABLE Customer_Table ( customer_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment, customer_link_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL, customer_link_type enum(booth, tradeshow) NOT NULL, customer_name varchar(30), customer_mail_list tinyint(1) UNSIGNED DEFAULT '0', ); CREATE TABLE TradeShow_Table ( tradeshow_id int(10) unsigned NOT NULL auto_increment, tradeshow_name varchar(100) NOT NULL default '' ) CREATE TABLE Booth_Table ( booth_id int(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment, booth_name varchar(30) NOT NULL ); - 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
