Re: Get fields in one field, separated by comma's
- Original Message - From: "Klaas Geldof" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, June 06, 2004 10:19 AM Subject: Re: Get fields in one field, separated by comma's > - Original Message - > From: "Victoria Reznichenko" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, June 05, 2004 9:50 PM > Subject: Re: Get fields in one field, separated by comma's > > > > "Klaas Geldof" <[EMAIL PROTECTED]> wrote: > > > > > > I have a question about MySQL queries. First I will explain the > situation, > > > then I will provide an example. > > > > > > > > > Situation: > > > > > > I have the tables 'offer', 'contact' and 'offer_contact'. Table 'offer' > > > contains data about offers, table 'contact' about contacts. Table > > > 'offer_contact' tells which contacts are associated with which offers > (since > > > offers can have multiple contacts associated with it and vice versa). > Now I > > > want to run a query so I get a row for each offer and in the result > there's > > > a field 'offer_contacts' which contains all the names of the contacts > > > associated with the offer on that row separated by comma's. > > > > > > > > > Example: > > > > > > Table 'offer' (offer_id - offer_title): > > > 1 - Test > > > 2 - Also test > > > > > > Table 'contact' (contact_id - contact_name): > > > 1 - John > > > 2 - Anna > > > > > > Table 'offer_contact' (offer_id - contact_id): > > > 1 - 1 > > > 1 - 2 > > > 2 - 1 > > > > > > Wanted result of query (offer_id - offer_name - offer_contacts): > > > 1 - Test - John, Anna > > > 2 - Also test - John > > > > > > > > > Is this possible? > > > > > > > GROUP_CONCAT() function may help. This function is available from version > 4.1: > > http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html > > Works great. Thanks! > > Just a little problem when I add a HAVING clause. For example this query: > > SELECT offer_id, GROUP_CONCAT(contact_name SEPARATOR ', ') AS contact_names > FROM offer > LEFT OUTER JOIN offer_contact ON offer_contact_offer_id = offer_id > LEFT OUTER JOIN contact ON offer_contact_contact_id = contact_id > GROUP BY offer_id > HAVING contact_names REGEXP 'john' > > This returns in the field 'contact_names' a comma-separated lists of the > contact names of an offer, but the last two characters of the list (that is, > the last two characters of the last name in the list) are gone! Without a > HAVING clause, the error does not occur. Is this a bug? I tested some things myself, and I think it's a bug. When I take SEPARATOR ',' (instead of ', ') only one character is missing at the end. With SEPARATOR '' no characters are missing... Does anyone know if this is fixed already (I'm using MySQL 4.1)? Or a way to work around it? Thanks. -- Klaas Geldof -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get fields in one field, separated by comma's
- Original Message - From: "Victoria Reznichenko" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, June 05, 2004 9:50 PM Subject: Re: Get fields in one field, separated by comma's > "Klaas Geldof" <[EMAIL PROTECTED]> wrote: > > > > I have a question about MySQL queries. First I will explain the situation, > > then I will provide an example. > > > > > > Situation: > > > > I have the tables 'offer', 'contact' and 'offer_contact'. Table 'offer' > > contains data about offers, table 'contact' about contacts. Table > > 'offer_contact' tells which contacts are associated with which offers (since > > offers can have multiple contacts associated with it and vice versa). Now I > > want to run a query so I get a row for each offer and in the result there's > > a field 'offer_contacts' which contains all the names of the contacts > > associated with the offer on that row separated by comma's. > > > > > > Example: > > > > Table 'offer' (offer_id - offer_title): > > 1 - Test > > 2 - Also test > > > > Table 'contact' (contact_id - contact_name): > > 1 - John > > 2 - Anna > > > > Table 'offer_contact' (offer_id - contact_id): > > 1 - 1 > > 1 - 2 > > 2 - 1 > > > > Wanted result of query (offer_id - offer_name - offer_contacts): > > 1 - Test - John, Anna > > 2 - Also test - John > > > > > > Is this possible? > > > > GROUP_CONCAT() function may help. This function is available from version 4.1: > http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html Works great. Thanks! Just a little problem when I add a HAVING clause. For example this query: SELECT offer_id, GROUP_CONCAT(contact_name SEPARATOR ', ') AS contact_names FROM offer LEFT OUTER JOIN offer_contact ON offer_contact_offer_id = offer_id LEFT OUTER JOIN contact ON offer_contact_contact_id = contact_id GROUP BY offer_id HAVING contact_names REGEXP 'john' This returns in the field 'contact_names' a comma-separated lists of the contact names of an offer, but the last two characters of the list (that is, the last two characters of the last name in the list) are gone! Without a HAVING clause, the error does not occur. Is this a bug? -- Klaas Geldof -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get fields in one field, separated by comma's
Hi, I have a question about MySQL queries. First I will explain the situation, then I will provide an example. Situation: I have the tables 'offer', 'contact' and 'offer_contact'. Table 'offer' contains data about offers, table 'contact' about contacts. Table 'offer_contact' tells which contacts are associated with which offers (since offers can have multiple contacts associated with it and vice versa). Now I want to run a query so I get a row for each offer and in the result there's a field 'offer_contacts' which contains all the names of the contacts associated with the offer on that row separated by comma's. Example: Table 'offer' (offer_id - offer_title): 1 - Test 2 - Also test Table 'contact' (contact_id - contact_name): 1 - John 2 - Anna Table 'offer_contact' (offer_id - contact_id): 1 - 1 1 - 2 2 - 1 Wanted result of query (offer_id - offer_name - offer_contacts): 1 - Test - John, Anna 2 - Also test - John Is this possible? Your help would be great! -- Klaas Geldof -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
