MySql Pivot Join Tables Problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
S1m0ne
Forum Newbie
Posts: 2
Joined: Fri Mar 29, 2013 3:13 pm

MySql Pivot Join Tables Problem

Post 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;
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: MySql Pivot Join Tables Problem

Post 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
S1m0ne
Forum Newbie
Posts: 2
Joined: Fri Mar 29, 2013 3:13 pm

Re: MySql Pivot Join Tables Problem

Post by S1m0ne »

Thank you so much requinix. I have it working now and understand how to do this in the future.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySql Pivot Join Tables Problem

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply