Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: How to write this SQL Query

    I have two tables:
    Table: Names Fields: ID, Name
    Table: Info Fields: ID, Key, Data

    Data looks like this:
    Names:
    1, Joe
    2, George

    Info:
    1, Address, 121 Main Street
    1, City, Smithfield
    1, State, OH
    2, Address, 345 Another Street

    I need a query that will show each name in Names along with any data associated with that name from Info.

    Suggestions?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by KingsleyHill View Post
    I need a query that will show each name in Names along with any data associated with that name from Info.
    Code:
    SELECT names.id
         , names.name
         , info.key
         , info.data
      FROM names
    LEFT OUTER
      JOIN info
        ON info.id = names.id
    ORDER
        BY names.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    3

    Get the data to a single line?

    Thanks for the SQL to get the data. Now...is there a way to get one line of data for each Names record?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you would do that with your application language, php or whatever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2011
    Posts
    3

    Is there a way to do this in MySQL?

    This is a reporting requirement so there is no higher level language. Is there a PL/SQL-type language available? Is there any way to do it in SQL?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by KingsleyHill View Post
    Is there any way to do it in SQL?
    sure
    Code:
    SELECT names.id
         , names.name
         , GROUP_CONCAT(
             CONCAT(info.key,':',info.data)
             SEPARATOR ', ' ) AS info
      FROM names
    LEFT OUTER
      JOIN info
        ON info.id = names.id
    GROUP
        BY names.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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