Combine Multiple tables into single row per record search results

I need to search two tables and combine the output in MySQL.  Here the table structure:

Table_1
-      UserID
-      First Name
-      Last Name

Table_2
-      UserID
-      FieldID (in this case, it is the question name)
-      Answer

Sample Data
Table_1
-      1      Joe              Smith
-      2      John      Doe
-      3      Jim              Jones

Table_2
-      1      Address1         123 Anywhere Lane
-      1      City                Somewhere
-      1      State            NY
-      1      What_is_your_favorite_color           green
-      1      What_is_your_favorite_shape      circle
-      2      Address1           345 Nowhere Drive
-      2      City                 Blahville

Note how table 2 is multiple records per userID and table 1 is single records per userID.

Desired output would combine tables 1 and 2 into a one row per record result, such as the following:

1   Joe    Smith    123 Anywhere Lane    Somwhere    NY    Green    Circle

Im not sure how to join/combine the search query to allow for table2, with multi-row results for a single UserID into a single row of results for that UserID.  Also, if possible, the search should allow for some cases where newer user records have addtional fieldIDs in table 2 that older ones don't.  If possible, a blank would be entered in that column for the older record.

I then need to make a PHP statement out of it, but I suppose that is for a different question!  :-)

Thanks

Brian

brianleesAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Hello brianlees,

The SQL statement could be...

SELECT t1.UserID, t1.FirstName, t1.LastName,
      (SELECT t2.Answer FROM Table_2 t2 WHERE t2.UserID = t1.UserID AND t2.FieldID = 'Address1') AS Address1,
      (SELECT t2.Answer FROM Table_2 t2 WHERE t2.UserID = t1.UserID AND t2.FieldID = 'City') AS City,
      (SELECT t2.Answer FROM Table_2 t2 WHERE t2.UserID = t1.UserID AND t2.FieldID = 'State') AS State,
      (SELECT t2.Answer FROM Table_2 t2 WHERE t2.UserID = t1.UserID AND t2.FieldID = 'What_is_your_favorite_color) AS Color,
      (SELECT t2.Answer FROM Table_2 t2 WHERE t2.UserID = t1.UserID AND t2.FieldID = 'What_is_your_favorite_shape') AS Shape
FROM Table_1 t1

Regards,

Patrick
0
 
oobaylyConnect With a Mentor Commented:
You could use GROUP_CONCAT, and then use php to split the returned Question & Answer field
SELECT Table_1.*
  GROUP_CONCAT(Table_2.FieldID SEPARATOR '|') AS `Questions`,
  GROUP_CONCAT(Table_2.Answer SEPARATOR '|') AS `Answers`
FROM Table_1
INNER JOIN Table_2 ON Table_1.UserID = Table_2.UserID
GROUP BY Table_1.UserID

Open in new window

0
 
brianleesAuthor Commented:
To all,

As you can imagine, I'm lining this data up for export (probably CSV).  by the way, this is a search in a WordPress mysql table.

matthewspatrick, is there a way to get it so I don't have to list each fieldID?

oobayly, I receive an error when I tried to use your search.  Here is the converted code I used:

SELECT wp_prxild_users.*
  GROUP_CONCAT(wp_pxrild_usermeta.meta_key SEPARATOR '|') AS `Questions`,
  GROUP_CONCAT(wp_pxrild_usermeta.meta_value SEPARATOR '|') AS `Answers`
FROM Table_1
INNER JOIN wp_pxrild_usermeta ON wp_prxild_users.id = wp_pxrild_usermeta.user_id
GROUP BY wp_prxild_users.id

Here is the error


#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT(wp_pxrild_usermeta.meta_key SEPARATOR '|') AS `Questions`,
  GROUP_' at line 2 

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
oobaylyCommented:
Sorry, there should be a comma after wp_prxild_users.*
SELECT wp_prxild_users.*
  GROUP_CONCAT(wp_pxrild_usermeta.meta_key SEPARATOR '|') AS `Questions`,
  GROUP_CONCAT(wp_pxrild_usermeta.meta_value SEPARATOR '|') AS `Answers`
FROM Table_1
INNER JOIN wp_pxrild_usermeta ON wp_prxild_users.id = wp_pxrild_usermeta.user_id
GROUP BY wp_prxild_users.id

Open in new window

0
 
oobaylyCommented:
Damn, forgot to paste the corrected code.
SELECT wp_prxild_users.*,
  GROUP_CONCAT(wp_pxrild_usermeta.meta_key SEPARATOR '|') AS `Questions`,
  GROUP_CONCAT(wp_pxrild_usermeta.meta_value SEPARATOR '|') AS `Answers`
FROM Table_1
INNER JOIN wp_pxrild_usermeta ON wp_prxild_users.id = wp_pxrild_usermeta.user_id
GROUP BY wp_prxild_users.id

Open in new window

0
 
brianleesAuthor Commented:
Here is the correct query....sort of (corrected spelling mistakes and also I missed that Table1 was still listed)

SELECT wp_pxrild_users.*,
  GROUP_CONCAT(wp_pxrild_usermeta.meta_key SEPARATOR '|') AS `Questions`,
  GROUP_CONCAT(wp_pxrild_usermeta.meta_value SEPARATOR '|') AS `Answers`
FROM wp_pxrild_users
INNER JOIN wp_pxrild_usermeta ON wp_prxild_users.ID = wp_pxrild_usermeta.user_id
GROUP BY wp_prxild_users.ID


I received the following error:
#1054 - Unknown column 'wp_prxild_users.ID' in 'on clause'

That is the correct field name.  ID is an auto-increment field in the table.
0
 
brianleesAuthor Commented:
Whoops....misspellings.

SELECT wp_pxrild_users.*,
  GROUP_CONCAT(wp_pxrild_usermeta.meta_key SEPARATOR '|') AS `Questions`,
  GROUP_CONCAT(wp_pxrild_usermeta.meta_value SEPARATOR '|') AS `Answers`
FROM wp_pxrild_users
INNER JOIN wp_pxrild_usermeta ON wp_pxrild_users.ID = wp_pxrild_usermeta.user_id
GROUP BY wp_pxrild_users.ID

That did it right.

So, I guess this is the only way to get the data out?  I can't have it so each field from the second table is listed as a column in the search output?
0
 
oobaylyCommented:
Well, matthewspatrick's idea is valid, but only if you know you're going to have a finite set of meta_key values. If you have effectively an infinte amount if meta_keys for any given user you'd have to have a stored procedure to dynamically create your query. And then you'd have to refrence the fields returned by index, so it's really a matter of six of one...
0
 
brianleesAuthor Commented:
We won't have an infinite number, but I bet we will have 8-10 total.
0
 
brianleesAuthor Commented:
Could I use a comma instead of a | in the output?  I'm trying to figure out how to get the CSV to work.
0
 
oobaylyConnect With a Mentor Commented:
Default is to use a comma, I just opted to you the pipe symbol as it's less likely to exists in strings being concatenated. Just remove SEPARATOR '|'

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.