Results 1 to 5 of 5
  1. #1
    Join Date
    May 2005
    Posts
    6

    Unanswered: mailing label query

    I have database of names and mailing address that I want to use to generate mailing labels. Some of the people who are in the database have the same address as others (they live in the same house) but they have their own entry. How can I generate a query that will list all the addresses without duplicating itself and show all the people who live in that one household?

    For example:
    John, Sue, and Mary Smith
    123 Main Street
    Town, ST 12345


    Thank you for your help in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use GROUP_CONCAT on the name field(s) when using GROUP BY on all the address fields
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2005
    Posts
    6
    I can't quite figure out the syntax. Can you tell me what I'm doing wrong?

    Code:
    SELECT GROUP_CONCAT(lname, fname) FROM roster GROUP BY lname
    Thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "list all the addresses without duplicating itself" == GROUP BY address fields

    also, if you want both lastname and firstname concatenated in the GROUP CONCAT, then you need a separate CONCAT function for them
    Code:
    SELECT GROUP_CONCAT(CONCAT(fname,' ',lname)) as who
         , address fields
      FROM roster
    GROUP
        BY address fields
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2005
    Posts
    6
    Perfect. Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •