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 MatthewsCommented:
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
 
oobaylyCommented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
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
 
oobaylyCommented:
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.