MySql Pivot Join Tables Problem
Posted: Fri Mar 29, 2013 3:34 pm
I am having trouble doing a pivoting a table in a join.
Here are my two tables:
user:
id firstname lastname city email
info_data:
id userid fieldid data
As you can see the info_data table is set up so that is can hold types of data dynamically, it is related to another table which simply holds the fieldid's and their names.
So in info_data there might be entries for the State, Place of Work, and Job title of a specific user
Ex:
info_data:
12 8 1 Texas
13 8 2 Where I Work
14 8 3 Web Programmer
So what I am trying to do is Join these two tables so that the data column in info_data will pivot to become apart of the rows returned about users.
Ex of query return that I want:
"8" "Jon Smith" "Brownsville" "jonsmith@altavistamail.com" "Texas" "Where I Work" "Web Programmer"
The problem that I am having is that I am getting returns like this:
"8" "Jon Smith" "Brownsville" "jonsmith@altavistamail.com" "Texas" "NULL" "NULL"
"8" "Jon Smith" "Brownsville" "jonsmith@altavistamail.com" "NULL" "Where I Work" "NULL"
"8" "Jon Smith" "Brownsville" "jonsmith@altavistamail.com" "NULL" "NULL" "Web Programmer"
I am getting all of the information but just not in the correct form. Here is the query I am currently using. Any help in explaining what I am doing wrong would be most appreciated.
SELECT user.id, user.firstname, user.lastname, user.city, user.email,
IF(info_data.fieldid='1', info_data.data, 'NULL') as 'work',
IF(info_data.fieldid='2', info_data.data, 'NULL') as 'job',
IF(info_data.fieldid='3', info_data.data, 'NULL') as 'state'
FROM user LEFT JOIN info_data ON user.id=info_data.userid;
Here are my two tables:
user:
id firstname lastname city email
info_data:
id userid fieldid data
As you can see the info_data table is set up so that is can hold types of data dynamically, it is related to another table which simply holds the fieldid's and their names.
So in info_data there might be entries for the State, Place of Work, and Job title of a specific user
Ex:
info_data:
12 8 1 Texas
13 8 2 Where I Work
14 8 3 Web Programmer
So what I am trying to do is Join these two tables so that the data column in info_data will pivot to become apart of the rows returned about users.
Ex of query return that I want:
"8" "Jon Smith" "Brownsville" "jonsmith@altavistamail.com" "Texas" "Where I Work" "Web Programmer"
The problem that I am having is that I am getting returns like this:
"8" "Jon Smith" "Brownsville" "jonsmith@altavistamail.com" "Texas" "NULL" "NULL"
"8" "Jon Smith" "Brownsville" "jonsmith@altavistamail.com" "NULL" "Where I Work" "NULL"
"8" "Jon Smith" "Brownsville" "jonsmith@altavistamail.com" "NULL" "NULL" "Web Programmer"
I am getting all of the information but just not in the correct form. Here is the query I am currently using. Any help in explaining what I am doing wrong would be most appreciated.
SELECT user.id, user.firstname, user.lastname, user.city, user.email,
IF(info_data.fieldid='1', info_data.data, 'NULL') as 'work',
IF(info_data.fieldid='2', info_data.data, 'NULL') as 'job',
IF(info_data.fieldid='3', info_data.data, 'NULL') as 'state'
FROM user LEFT JOIN info_data ON user.id=info_data.userid;