Query headaches
Hey list, I've been tinkering with a query the past few hours, and it's about to drive me coconuts. It's using quite a few (messy) control statements, so I guess I'm staring myself blind trying to solve it. Could be a combination of that, and too much coffee. Anyway, here's the query: http://pastebin.perl.se/b6e5b7f1cb/ The error I get while trying executing is Unknown column 'products.product_id' in 'on clause'. Since I am selecting all columns from that table (products), I can't really see why there's a fuss about it! I'm thankful for every tip I can get, and please let me know if you want to see the table layouts. Best regards -- Kim Christensen With insomnia, you're never really asleep; you're never really awake. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
On 8/31/06, Harrison Fisk [EMAIL PROTECTED] wrote: Hello, On Aug 31, 2006, at 5:12 AM, Kim Christensen wrote: Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. The problem is that your product_id is an INT in one table and a VARCHAR in the other. This will cause MySQL to have to do conversions of data types, which means it can't use indexes. Switch product_id to be an INT and both tables and it should solve your performance problems. Ouch. I reduced the DELETE... query time to 0.4 secs in total after changing the second table's product_id to an INT. Thanks alot, I sure wouldn't have seen this on my own after staring myself blind on this - you've saved me lots of work! I'm sure gonna be more careful with the data types next time! :-) -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insane execution time for JOIN query
Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. Now, each night I run a query which removes all rows from the items table which haven't been updated in the last 24 hours. This works just fine, so here's my actual problem: Every now and then, all rows for a specific product in the items table gets removed after the nightly update, and I'm working on a query which removes all rows in the products table that doesn't have any corresponding rows (matched with product_id) in the items table. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Now, my query has been running for 6 hours straight, and it's marked as Sending data in the process list. Any ideas? products has ~113.500 records while items has ~123.439. Table descriptions: PRODUCTS ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | | product_retail_id | varchar(255) | NO | MUL | | | | manufactor_id | int(10) unsigned | NO | | 0 | | | product_description| varchar(255) | NO | | | | | product_comment| text | NO | | | | | product_internal_id| varchar(255) | NO | | | | | product_price1_percent | float| NO | | 0 | | | product_price2_percent | float| NO | | 0 | | | product_price3_percent | float| NO | | 0 | | | product_price1_amount | float| NO | | 0 | | | product_price2_amount | float| NO | | 0 | | | product_price3_amount | float| NO | | 0 | | | product_added | int(10) unsigned | NO | | 0 | | | product_url| varchar(255) | NO | | | | | product_ean| varchar(13) | NO | | | | ++--+--+-+-++ ITEMS: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | item_id | int(10) unsigned | NO | PRI | | auto_increment | | product_id | varchar(45) | NO | MUL | 0 || | item_price | float| NO | | 0 || | item_stock | int(11) | NO | | 0 || | item_incoming| varchar(45) | NO | | || | item_updated | int(10) unsigned | NO | | 0 || | item_url | varchar(255) | NO | | || | supplier_id | int(10) unsigned | NO | | 0 || | item_internal_id | varchar(45) | NO | MUL | || | item_description | varchar(255) | NO | | || | item_weight | float| NO | | 0 || +--+--+--+-+-++ Best regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
On 8/31/06, Brent Baisley [EMAIL PROTECTED] wrote: How about posting the results of: EXPLAIN SELECT products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; ++-+--+--+---+--+-+--++-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+--+-+--++-+ | 1 | SIMPLE | products | ALL | | | | | 89447 | | | 1 | SIMPLE | items| ALL | PRICE_INFO| | | | 123223 | Using where; Not exists | ++-+--+--+---+--+-+--++-+ As you can see I have an index (UNIQUE) set that I use for an on INSERT... ON DUPLICATE method while updating the prices every morning, is this the messy part? Your query looks fine to me. 6 hours is a very long time for tables that small. Are these MyISAM or InnoDB? They are both MyISAM, with no other options changed from default. - Original Message - From: Kim Christensen [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, August 31, 2006 5:12 AM Subject: Insane execution time for JOIN query Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. Now, each night I run a query which removes all rows from the items table which haven't been updated in the last 24 hours. This works just fine, so here's my actual problem: Every now and then, all rows for a specific product in the items table gets removed after the nightly update, and I'm working on a query which removes all rows in the products table that doesn't have any corresponding rows (matched with product_id) in the items table. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Now, my query has been running for 6 hours straight, and it's marked as Sending data in the process list. Any ideas? products has ~113.500 records while items has ~123.439. Table descriptions: PRODUCTS ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | | product_retail_id | varchar(255) | NO | MUL | | | | manufactor_id | int(10) unsigned | NO | | 0 | | | product_description| varchar(255) | NO | | | | | product_comment| text | NO | | | | | product_internal_id| varchar(255) | NO | | | | | product_price1_percent | float| NO | | 0 | | | product_price2_percent | float| NO | | 0 | | | product_price3_percent | float| NO | | 0 | | | product_price1_amount | float| NO | | 0 | | | product_price2_amount | float| NO | | 0 | | | product_price3_amount | float| NO | | 0 | | | product_added | int(10) unsigned | NO | | 0 | | | product_url| varchar(255) | NO | | | | | product_ean| varchar(13) | NO | | | | ++--+--+-+-++ ITEMS: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | item_id | int(10) unsigned | NO | PRI | | auto_increment | | product_id | varchar(45) | NO | MUL | 0 | | | item_price | float| NO | | 0 | | | item_stock | int(11) | NO | | 0 | | | item_incoming| varchar(45) | NO | | | | | item_updated | int(10
Re: Insane execution time for JOIN query
On 8/31/06, Brent Baisley [EMAIL PROTECTED] wrote: hmmm, not sure why it's only scanning 89K records from the products table, I would think it would scan the whole table. It is scanning the entire items table, which I would think it wouldn't do. Well, the query in question actually did do some work, the current row count IS 89k. My bad. The speed is still an issue though! How about posting your SHOW INDEX FROM items result. +---+++--+--+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+--+---+-+--++--++-+ | items | 0 | PRIMARY| 1| item_id | A | 123223 | NULL || | BTREE | | | items | 0 | PRICE_INFO | 1| product_id | A | NULL| NULL || | BTREE | | | items | 0 | PRICE_INFO | 2| supplier_id | A | 123223 | NULL || | BTREE | | | items | 1 | retail_id | 1| item_internal_id | A | 123223 | NULL || | BTREE | | +---+++--+--+---+-+--++--++-+ Also, what are your memory settings? SHOW VARIABLES LIKE %buffer_size +-+--+ | Variable_name | Value| +-+--+ | bulk_insert_buffer_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072 | | key_buffer_size | 16777216 | | myisam_sort_buffer_size | 8388608 | | preload_buffer_size | 32768| | read_buffer_size| 131072 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | +-+--+ The box has 1GB of physical RAM and 2GB in one swap partition. Thanks for the help so far! -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lazy and greedy control statements
Hey list; I have a case where I need to fetch a product row from a table containing price information about some products, and where every product can have multiple rows but from different suppliers (thus with different prices and stock information). However, I'm trying to create a query which fetches the row matching a product from the supplier with the best price, but where the stock is 0. If none of the suppliers has the item in stock, it should get the row with the highest price of them all. Is this possible with a SQL query, or do I need to fetch all rows for the product and then process it with my programming language of choice? :-) Best Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP_CONCAT returns BLOB
Hey list; I'm having trouble with the GROUP_CONCAT() function, which (according to the docs) is supposed to give me a column with the VARCHAR type, unless group_concat_max_len is 512. Instead it BLOBs me! Now, I haven't set any value for the group_concat_max_len, so it shouldn't be different from the default value, but the strange thing is that the same function returns a VARCHAR column when used on another table - they are both MyISAM tables. Here's the query in question: SELECT GROUP_CONCAT(product_id) FROM products WHERE product_retail_id LIKE '%dfl-%' Any ideas? Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP_CONCAT returns BLOB
Hey list; I'm having trouble with the GROUP_CONCAT() function, which (according to the docs) is supposed to give me a column with the VARCHAR type, unless group_concat_max_len is 512. Instead it BLOBs me! Now, I haven't set any value for the group_concat_max_len, so it shouldn't be different from the default value, but the strange thing is that the same function returns a VARCHAR column when used on another table - they are both MyISAM tables. Here's the query in question: SELECT GROUP_CONCAT(product_id) FROM products WHERE product_retail_id LIKE '%dfl-%' Any ideas? (Sorry if this is a double-post, I accidently sent the first message from an unsubsribed address) -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP_CONCAT returns BLOB
On 6/21/06, Kim Christensen [EMAIL PROTECTED] wrote: Hey list; I'm having trouble with the GROUP_CONCAT() function, which (according to the docs) is supposed to give me a column with the VARCHAR type, unless group_concat_max_len is 512. Instead it BLOBs me! Hmm, I just realized this won't make any difference to my applications. Sorry! *Beats himself to death with his MySQL handbook* -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP_CONCAT returns BLOB
On 6/21/06, Kim Christensen [EMAIL PROTECTED] wrote: Hey list; I'm having trouble with the GROUP_CONCAT() function, which (according to the docs) is supposed to give me a column with the VARCHAR type, unless group_concat_max_len is 512. Instead it BLOBs me! Hmm, I just realized this won't make any difference to my applications. Sorry! *Beats himself to death with his MySQL handbook* Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BOOLEAN search with asterisk as preceeding operand?
Hey gang; If I have understood the boolean search method correctly, from own experiments and the docs, the asterisk operand cannot be put before a word - it negates the preceeding word completely. How have you solved this? I want my searches to match both words that starts with, contains, and ends with keyword. I guess I'm not alone to have this problem ;-) Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order field of a table
On 6/15/06, theo [EMAIL PROTECTED] wrote: Now, there is a new employee John with a job better than Peter's His importance is one more than peter's, and all other move up +1; So the list should look like this after inserting John: Peter; 24years, some job, 0 John; 28years, somewhat better job, 1 Anna; 22years, better job, 2 Frank; 40 years, chief, 3 Which is the most effiicient way (SQL Statements) to insert or delete John in this example? Barrys solution works fine, but you might want a little more details (no offence Bar!) :-) Before inserting a new employee: UPDATE workers SET importance = importance + 1 WHERE importance X Where X is the position that the new employee should have. This makes every person under the new employee raise the importance value by one. Just reverse the method before removing an employee: UPDATE workers SET importance = importance - 1 WHERE importance X importance 0 Which would move all the employees which were under the given employee (with importance X) up a step, as long as they're not on top (importance 0). ...just realize this might get you more confused than clearing it out for you, but what the hey. Best regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can i decrypt password
On 6/13/06, Barry [EMAIL PROTECTED] wrote: Kaushal Shriyan schrieb: Hi I have a situation here how can i decrypt the mysql database userrs password Kaushal Not possible. It is possible. However, that depends on which version of MySQL you are using - and which crypt method you are using for the passwords. -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limiting results from joins
Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id; Now, each unique product_id from products can have more than one entry in the items table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column item_updated from the items table equals 1. Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limiting results from joins
On 6/12/06, Barry [EMAIL PROTECTED] wrote: Kim Christensen schrieb: Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id; Now, each unique product_id from products can have more than one entry in the items table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column item_updated from the items table equals 1. Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? Regards Use INNER JOIN :) That worked great, really cut the time on my queries! However, how would one filter out the INNER JOIN statement if it returns more than one row to JOIN? I have reworked my previous table setup and query so that I need the row that has the lowest value of item_updated, not particularly 1. Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. As it is right now, MySQL returns a row for each time the product occurs in the items table, which is not what I want :-) Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limiting results from joins
On 6/12/06, Barry [EMAIL PROTECTED] wrote: Kim Christensen schrieb: Hey list; Consider this statement: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id; Now, each unique product_id from products can have more than one entry in the items table, but I only want to fetch the one which fullfills a certain criteria. In this case, I want the statement only to JOIN the row if the column item_updated from the items table equals 1. Is this the proper way to solve this: SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id = m.manufactor_id p.product_id = i.product_id i.item_id = (SELECT item_id FROM items i2 WHERE i2.item_updated = 1); I find the above solution VERY slow, almost as if I have missed out on a very fundamental part of the logic - but it does get the work done. How could I speed this up, or solve it in another statement? Regards Use INNER JOIN :) That worked great, really cut the time on my queries! However, how would one filter out the INNER JOIN statement if it returns more than one row to JOIN? I have reworked my previous table setup and query so that I need the row that has the lowest value of item_updated, not particularly 1. Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. As it is right now, MySQL returns a row for each time the product occurs in the items table, which is not what I want :-) (Sorry if this is a dupe post, but I sent it from the wrong address) -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limiting results from joins
On 6/12/06, Peter Lauri [EMAIL PROTECTED] wrote: -- Here's what it looks like right now: SELECT * FROM products p INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id INNER JOIN items i ON i.product_id = p.product_id The problem is, that each entry in products may occur more than once in items, and they are identified by product_id in both tables. How do I filter out the results from the last INNER JOIN by certain criterias? I want the INNER JOIN to only return the row from items which has the lowest value in the column item_updated. -- Just add: ORDER BY i.item_updated GROUP BY i.product_id (assuming that item_updated and product_id are in table i) That gives me the result that I'm after, the only problem seems to be that the sorting is made before the matching - on all the rows (huge execution time) - how can I prevent this? MVH -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A complex JOIN scenario
Hello everyone I have a pretty complex (at least for me) scenario which I really can't work out the logic of. I have two tables with the following layout: table 1: invoice -- invoice_id order_id customer_id invoice_timestamp invoice_total ...etc (only unique names) table 2: invoice_archive -- invoice_id invoice_journal_id By doing a UNION select on the invoice_id's of these two tables, I get a list of ID's from both of them. Since they are unique, this works pretty good - but I want all the columns aswell! I've seen some JOIN examples before, which fetches values from two tables (B and C) based on values from table A, but since the ID is only present in one of them at a time in my scenario, I really can't work it out. I want to have my output displayed something like this: ++--+-+---+---++ | invoice_id | order_id | customer_id | invoice_timestamp | invoice_total | invoice_journal_id | ++--+-+---+---++ | 1 | 1000 | 1 | 123131332131 | 1232 | NULL | | 2 | NULL |NULL | NULL | NULL | 21 | ++--+-+---+---++ As you probably understand from this example above, each invoice_id is only present in one of the tables, therefore the NULL values. How can I accomplish something like this? -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A complex JOIN scenario
On 4/11/06, Martijn Tonies [EMAIL PROTECTED] wrote: Kim, I have a pretty complex (at least for me) scenario which I really can't work out the logic of. I have two tables with the following layout: table 1: invoice -- invoice_id order_id customer_id invoice_timestamp invoice_total ...etc (only unique names) table 2: invoice_archive -- invoice_id invoice_journal_id By doing a UNION select on the invoice_id's of these two tables, I get a list of ID's from both of them. Since they are unique, this works pretty good - but I want all the columns aswell! I've seen some JOIN examples before, which fetches values from two tables (B and C) based on values from table A, but since the ID is only present in one of them at a time in my scenario, I really can't work it out. What exactly do you mean by present in one of them? Can you give us the sample data from which you derive the result below? I mean that my two tables (invoice and invoice_archive) both share a common column name, invoice_id, and in my result example below I have to rows, one in each table. Am I too unclear? Note that my example is not a realy result set, it's only here to show you what kind of query I'm trying to build! I want to have my output displayed something like this: ++--+-+---+---+- ---+ | invoice_id | order_id | customer_id | invoice_timestamp | invoice_total | invoice_journal_id | ++--+-+---+---+- ---+ | 1 | 1000 | 1 | 123131332131 | 1232 | NULL | | 2 | NULL |NULL | NULL | NULL | 21 | ++--+-+---+---+- ---+ As you probably understand from this example above, each invoice_id is only present in one of the tables, therefore the NULL values. How can I accomplish something like this? See above. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- Kim Christensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A complex JOIN scenario
Note that my example is not a realy result set, it's only here to show you what kind of query I'm trying to build! Yes, that I understand. But WHAT is your current data in both tables from which you can/should derive your wanted resultset? invoice_archive: ++--++ | invoice_id | order_id | invoice_journal_id | ++--++ | 1062 | 1035 | 6 | | 1063 | 1036 | 6 | | 1064 | 1037 | 8 | ++--++ invoice: ++--+-+---+---+ | invoice_id | order_id | customer_id | invoice_timestamp | invoice_total | ++--+-+---+---+ | 1065 | 1038 | 63 | 1144744655| 777 | ++--+-+---+---+ I hope this helps, sorry for being short on information but I haven't really woken up properly yet. More coffee.. -- Kim Christensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A complex JOIN scenario
If this needs to give you this result: ++--++---+-- -+ | invoice_id | order_id | invoice_journal_id |invoice_timestamp | invoice_total | ++--++---+-- -+ | 1062 | 1035 | 6 | NULL | NULL | NULL | | 1063 | 1036 | 6 |..more NULL ... | 1064 | 1037 | 8 | | 1065 | 1038 | NULL| 1144744655| 777 | then a UNION is the only thing you can do. It's not a JOIN at all. Oh wait, you can call it a FULL JOIN, which simply returns results from both tables in the join, but hey, that's actually the lack of a join, IMO :-) Yeah, I guess a FULL one is exactly what I think I'm looking for. Still can't work the query out though, since both columns have different column lengths MySQL is complaining - I need to know how to NULL the non-existant table columns for the rows that doesn't have any values for them. Any hints? ;-) -- Kim Christensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A complex JOIN scenario
On 4/11/06, Santino [EMAIL PROTECTED] wrote: I do not try it : select invoice_id, order_id, customer_id, invoice_timestamp, invoice_total, null from invoice union all select invoice_id, null, null, null, null, invoice_journal_id from invoice_archive Works like a charm, thanks! -- Kim Christensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User is rejected because of IP, but hostname is allowed
On 2/16/06, Ryan Stille [EMAIL PROTECTED] wrote: Nope. Well, have you tried it? Did it solve your problem? -- Kim Christensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User is rejected because of IP, but hostname is allowed
On 2/16/06, Ryan Stille [EMAIL PROTECTED] wrote: Nope. Well, have you tried it? Did it solve your problem? -- Kim Christensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating REGEXP patterns with use of subquery
Is there any way to build a REGEXP pattern set by using a subquery? I have a set of rows in table table, with the column value being target for my query. That column's content is a bracket separated list of values, like this: [118][Word][Integer][Stuff]... [67][Another word][Another integer][More stuff]... Now, to get all rows which value string starts with [118], this does the trick: SELECT * FROM table WHERE value REGEXP '^\\[118' And further on, to get all rows which value string starts with either [21], [42] or [999], this works fine: SELECT * FROM table WHERE value REGEXP '^\\[(21|42|999)' But I need to be able to do this last query without having to specify the values by hand, but with a subquery. Imagine the following query: SELECT id FROM items WHERE parent=5 This gives me a result set of rows which parent columns matches 5. I would like to use all these results in the last REGEXP query example above, something like this: SELECT * FROM table WHERE value REGEXP '^\\[(SELECT id FROM items WHERE parent=5)' Anyone got any clues? -- Kim Christensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
