Page 1 of 1

MySql Pivot Join Tables Problem

Posted: Fri Mar 29, 2013 3:34 pm
by S1m0ne
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;

Re: MySql Pivot Join Tables Problem

Posted: Fri Mar 29, 2013 10:51 pm
by requinix
For a pivot you need a JOIN for every new field you want to add.

Code: Select all

SELECT
	user.id, user.firstname, user.lastname, user.city, user.email,
	workt.data AS work,
	jobt.data AS job,
	statet.data AS state
FROM user
LEFT JOIN info_data AS workt ON user.id = workt.userid AND workt.fieldid = 2
LEFT JOIN info_data AS jobt ON user.id = jobt.userid AND jobt.fieldid = 3
LEFT JOIN info_data AS statet ON user.id = statet.userid AND statet.fieldid = 1

Re: MySql Pivot Join Tables Problem

Posted: Sat Mar 30, 2013 12:41 am
by S1m0ne
Thank you so much requinix. I have it working now and understand how to do this in the future.

Re: MySql Pivot Join Tables Problem

Posted: Tue May 14, 2013 6:57 am
by VladSun
How about:
[sql]
SELECT
user.*,
MAX(IF(info_data.fieldid = 2, info_data.`data`, NULL)) AS work,
MAX(IF(info_data.fieldid = 3, info_data.`data`, NULL)) AS job,
MAX(IF(info_data.fieldid = 1, info_data.`data`, NULL)) AS state
FROM
user
LEFT JOIN
info_data ON
user.id = info_data.userid
GROUP BY
user.id
[/sql]