----- 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]
