Solved

Combine Multiple tables into single row per record search results

Posted on 2009-04-13
12
775 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now