Solved

Combine Multiple tables into single row per record search results

Posted on 2009-04-13
12
781 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:brianlees
  • 5
  • 5
12 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 84 total points
ID: 24132086
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
 
LVL 15

Assisted Solution

by:oobayly
oobayly earned 168 total points
ID: 24132111
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
 

Author Comment

by:brianlees
ID: 24132218
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:oobayly
ID: 24132225
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24132239
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
 

Author Comment

by:brianlees
ID: 24132576
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
 

Author Comment

by:brianlees
ID: 24132830
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
 
LVL 15

Expert Comment

by:oobayly
ID: 24132919
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
 

Author Comment

by:brianlees
ID: 24134089
We won't have an infinite number, but I bet we will have 8-10 total.
0
 

Author Comment

by:brianlees
ID: 24134097
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
 
LVL 15

Assisted Solution

by:oobayly
oobayly earned 168 total points
ID: 24135592
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question