RE: Collapsing queries
Try this: SELECT * FROM content, address WHERE content.msg_id = address.msg_id If I understand what your asking for, you want a JOIN. If I don't understand, forgive me, it's late... :) Tyrone -Original Message- From: matteo [mailto:matt@web]On Behalf Of Matthieu Paindavoine Sent: Thursday, June 14, 2001 10:00 PM To: [EMAIL PROTECTED] Subject: Collapsing queries Hello, I am relatively new with MySQL (and SQL for that matter) and I couldn't find much reference to my problem in earlier posts. Let's say I have 2 tables, CONTENT: msg_id, msg_text ADDRESS: msg_id, address and I receive the msg Hello World addressed to the twins Foo and Bar. now my tables look like this: CONTENT: 1 Hello World ADDRESS: 1 Foo 1 Bar I am desperatly looking for a sql statement that would produce 1 | Hello World | Foo, Bar Is there such a thing?? I welcome any suggestions, Thanks Matthieu - 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: Collapsing queries
Thank you for your help but this is not quite what I wanted. I tried your solution, but it raises the following problem: It would return 1 | Hello World | Foo 1 | Hello World | Bar and if I neglect to look at the msg_id in the first column (which I hope I can get rid of) then there is no way of saying if it's one msg to Foo and Bar, or if it's two messages, one for Foo and one for Bar. So when i said in the subject header collapsing, I really meant it ;-) Do you have more resources? Cheers, Matt --- SELECT * FROM content, address WHERE content.msg_id = address.msg_id If I understand what your asking for, you want a JOIN. If I don't understand, forgive me, it's late... :) Hello, I am relatively new with MySQL (and SQL for that matter) and I couldn't find much reference to my problem in earlier posts. Let's say I have 2 tables, CONTENT: msg_id, msg_text ADDRESS: msg_id, address and I receive the msg Hello World addressed to the twins Foo and Bar. now my tables look like this: CONTENT: 1 Hello World ADDRESS: 1 Foo 1 Bar I am desperatly looking for a sql statement that would produce 1 | Hello World | Foo, Bar Is there such a thing?? I welcome any suggestions, Thanks Matthieu - 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: Collapsing queries
this is as close as I could come to what you want. it involves 2 steps set a variable set @var:= ''; then query select address.msg_id, content.msg_text, @var:= CONCAT_WS(',',@var,address.address) as addresses from content, address where content.msg_id = address.msg_id; this returns a list but if you grap the last one in the list it will be what you want ++-+---+ | msg_id | msg_test| addresses | ++-+---+ | 1 | Hello World | Foo | | 1 | Hello World | Foo,Bar | --- it will always be the last one you can't reorder ++-+---+ that's as close as I could figure -Original Message- From: Matthieu Paindavoine [mailto:[EMAIL PROTECTED]] Sent: Friday, June 15, 2001 7:23 AM To: [EMAIL PROTECTED] Subject: Re: Collapsing queries Thank you for your help but this is not quite what I wanted. I tried your solution, but it raises the following problem: It would return 1 | Hello World | Foo 1 | Hello World | Bar and if I neglect to look at the msg_id in the first column (which I hope I can get rid of) then there is no way of saying if it's one msg to Foo and Bar, or if it's two messages, one for Foo and one for Bar. So when i said in the subject header collapsing, I really meant it ;-) Do you have more resources? Cheers, Matt --- SELECT * FROM content, address WHERE content.msg_id = address.msg_id If I understand what your asking for, you want a JOIN. If I don't understand, forgive me, it's late... :) Hello, I am relatively new with MySQL (and SQL for that matter) and I couldn't find much reference to my problem in earlier posts. Let's say I have 2 tables, CONTENT: msg_id, msg_text ADDRESS: msg_id, address and I receive the msg Hello World addressed to the twins Foo and Bar. now my tables look like this: CONTENT: 1 Hello World ADDRESS: 1 Foo 1 Bar I am desperatly looking for a sql statement that would produce 1 | Hello World | Foo, Bar Is there such a thing?? I welcome any suggestions, Thanks Matthieu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Collapsing queries
SELECT cont.msg_id, cont.msg_text, CONCAT_WS(', ', a1.address, a2.address) FROM CONTENT AS cont, ADDRESS AS a1, ADDRESS AS a2 WHERE cont.msg_id = a1.msg_id AND cont.msg_id = a2.msg_id; Many thanks to salle in efnet #mysql for teaching me this trick ;-) Hello, I am relatively new with MySQL (and SQL for that matter) and I couldn't find much reference to my problem in earlier posts. Let's say I have 2 tables, CONTENT: msg_id, msg_text ADDRESS: msg_id, address and I receive the msg Hello World addressed to the twins Foo and Bar. now my tables look like this: CONTENT: 1 Hello World ADDRESS: 1 Foo 1 Bar I am desperatly looking for a sql statement that would produce 1 | Hello World | Foo, Bar Is there such a thing?? - 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