How to write this SQL Query

KingsleyHill · #1 (**permalink**) 09-29-11, 20:13

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?

r937 · #2 (**permalink**) 09-30-11, 03:17

Quote:

Originally Posted by KingsleyHill

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

KingsleyHill · #3 (**permalink**) 09-30-11, 08:41

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

r937 · #4 (**permalink**) 09-30-11, 09:25

you would do that with your application language, php or whatever

KingsleyHill · #5 (**permalink**) 09-30-11, 10:58

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?

r937 · #6 (**permalink**) 09-30-11, 11:29

Quote:

Originally Posted by KingsleyHill

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

Internet Services

Web Development

Digital Marketing

Consumer Tech