Solved

Combine Multiple tables into single row per record search results

Posted on 2009-04-13
12
790 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

 
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

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 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