Query pivot 2 tables

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Query pivot 2 tables

Postby kraadde » 10. February 2017 16:37

I am just close to madness... I am trying to figure out how to achieve a query that do the following.
I have a table users and a table user.profile in my joomla application. The users table has about 1800 records.
I just show below dummy tables:

table users

ID name surname dob
1 Smith John 06.01.2000
2 Doe Joe 25.08.1958
3 Meyer Holly 09.05.1960


table user_profiles
ID profile-key profile.value
1 profile.tel 123 453 456
1 profile.mobile 235 445 23
1 profile.address 22, Church Street
1 profile.city Dodge city
2 profile.tel 565 5654 32
2 profile.mobile 2528 25 1213
2 profile.address 1234, 6th Avenue
2 profile.city New London
3 profile.tel 457 578 45
3 profile.mobile 121 545 88
3 profile.address 3, North Street
3 profile.city Chicago



What I want is to achieve the following result:

Table 3
ID name surname dob profile.tel profile.mobile profile.address profile.city
1 Smith John 06.01.2000 123 453 456 235 445 23 22, Church Street Dodge city
2 Doe Joe 25.08.1958 565 5654 32 2528 25 1213 1234, 6th Avenue New London
3 Meyer Holly 09.05.1960 457 578 45 121 545 88 3, North Street Chicago

Now some SQL allows PIVOT command, but MySQLi does not. Can anyone show me how to get this task done?

What I am doing with table3 is to allow me to create a change table when compared with an other excel table with similar content in order to allow them to be "synchronized". Althogh not the best idea to have 2 separate tables with similar content, that is the way is is right now.

Thanks a lot for your help.

Adri

Thanks

Adri
kraadde
 
Posts: 13
Joined: 10. February 2017 16:09
XAMPP version: 5.6.30
Operating System: MS Win10

Re: Query pivot 2 tables

Postby Nobbie » 10. February 2017 19:28

Code: Select all
SELECT
users.id, users.name, users.surname, users.dob, user_profiles.tel, user_profiles.mobile, user_profiles.address, user_profiles.city
FROM users, user_profiles
WHERE users.ID = user_profiles.ID


I have no idea what the hell you mean by PIVOT (its known from Excel?!), this is a absolutely basic SQL statement, i have no idea what you mean by "MySQLi does not" as MySQLi is only an API and not an SQL Engine. FInally, you are totally wrong here, this is the Xampp Support Forum for Windows. For more questions about SQL you should go for an SQL Forum.
Nobbie
 
Posts: 9509
Joined: 09. March 2008 13:04

Re: Query pivot 2 tables

Postby Altrea » 10. February 2017 20:46

The difficulty is that user_profiles.tel, etc are not table colums. This data is stored like a key value pair in a generic table. This was heavily used to give endusers the possibility to add custom fields without the need of changing the database structure.

I don't know if this problem is solvable without heavy use of subqueries.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8777
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Query pivot 2 tables

Postby Nobbie » 10. February 2017 22:10

Altrea wrote:The difficulty is that user_profiles.tel, etc are not table colums. This data is stored like a key value pair in a generic table.


OMG - i thought that this is only here in order to display the table. What a horrible table.

A quick and easy solution would be to divide the profiles table into (temporary) tables and finally join all tables with the user table. I dont think that there is more sophisticated solution.

Code: Select all
CREATE TEMPORARY TABLE profile_tel (SELECT ID, value from user_profiles WHERE key = "tel");
CREATE TEMPORARY TABLE profile_mobile (SELECT ID, value from user_profiles WHERE key = "mobile");
CREATE TEMPORARY TABLE profile_address (SELECT ID, value from user_profiles WHERE key = "address");
CREATE TEMPORARY TABLE profile_city (SELECT ID, value from user_profiles WHERE key = "city");

SELECT user.ID, user.name, user.surname, user.dob, profile_tel.value, profile_mobile.value, profile_address.value, profile_city.value from user, profile_tel, profile_mobile, profile_address, profile_city
WHERE user.ID = profile_tel.ID AND user.ID = profile_mobile.ID AND user.ID = profile_address.ID AND user.ID = profile_city.ID;
Nobbie
 
Posts: 9509
Joined: 09. March 2008 13:04

Re: Query pivot 2 tables

Postby Altrea » 10. February 2017 23:43

Code: Select all
SELECT
  `u`.`ID`,
  `u`.`name`,
  `u`.`surname`,
  `u`.`dob`,
  `profile.tel`.`profile.value`
FROM
  `users` `u`
JOIN
  `user_profiles` `profile.tel`
  ON `u`.`ID` = `profile.tel`.`ID` AND `profile.tel`.`profile-key` = 'profile.tel'
...


very sloppy, but... :D

Idea reference from here: http://stackoverflow.com/questions/4287 ... alue-pairs
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8777
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Query pivot 2 tables

Postby Nobbie » 11. February 2017 01:22

What is profile.tel?
Nobbie
 
Posts: 9509
Joined: 09. March 2008 13:04

Re: Query pivot 2 tables

Postby kraadde » 11. February 2017 01:35

tel for telephone number
kraadde
 
Posts: 13
Joined: 10. February 2017 16:09
XAMPP version: 5.6.30
Operating System: MS Win10

Re: Query pivot 2 tables

Postby Nobbie » 11. February 2017 11:58

kraadde wrote:tel for telephone number


Funny answer.

What is `profile.tel`?? There is neither a table `profile`, nor a column `tel` in your data. So what is `profile.tel`? As far as I understood, "profile.tel" is a (runtime) value of the column profile-key from the table user_profiles - so there is no column `profile.tel`, that Select cannot work.
Nobbie
 
Posts: 9509
Joined: 09. March 2008 13:04

Re: Query pivot 2 tables

Postby Altrea » 11. February 2017 12:27

profile.tel is an Alias i choose for the JOIN. You can also name them different like up1, up2, etc, but for each of the 4 key value pairs you will need a join with their own Alias.

Here is a full example:

Code: Select all
SELECT
  `u`.`ID`,
  `u`.`name`,
  `u`.`surname`,
  `u`.`dob`,
  `up1`.`profile.value` AS `tel`,
  `up2`.`profile.value` AS `mobile`,
  `up3`.`profile.value` AS `address`,
  `up4`.`profile.value` AS `city`
FROM
  `users` AS `u`
JOIN
  `user_profiles` AS `up1`
  ON (`u`.`ID` = `up1`.`ID` AND `up1`.`profile-key` = 'profile.tel')
JOIN
  `user_profiles` AS `up2`
  ON (`u`.`ID` = `up2`.`ID` AND `up2`.`profile-key` = 'profile.mobile')
JOIN
  `user_profiles` AS `up3`
  ON (`u`.`ID` = `up3`.`ID` AND `up3`.`profile-key` = 'profile.address')
JOIN
  `user_profiles` AS `up4`
  ON (`u`.`ID` = `up4`.`ID` AND `up4`.`profile-key` = 'profile.city')


If any of the users does have an incomplete profile info (so not all of the 4 profile values are set) you need to use a different JOIN Type like LEFT JOIN.
This method will not really work if a profile value is set twice in the database (a user does have two mobile numbers set for example). The database structure cannot prevent this.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8777
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Query pivot 2 tables

Postby kraadde » 11. February 2017 13:11

@Nobbie

you have asked about profile.tel
Now if you see the table user_profiles, it consist of 3 column, ie. ID, profile-key and profile.value
The first record is
ID= 1
profile-key= profile.tel
profile.value= 123 453 456

Hope this clarifies your question.

Adri
kraadde
 
Posts: 13
Joined: 10. February 2017 16:09
XAMPP version: 5.6.30
Operating System: MS Win10

Re: Query pivot 2 tables

Postby Nobbie » 11. February 2017 14:00

Altrea wrote:Here is a full example:


Ok, now i got. It is basically the same solution as i suggested, for better readability i created four temporary tables instead of using the same table in a join. Of course is is quite ugly anyway, because of the "meta" data modell of the user_profiles table (this is a typical Excel table, but not an SQL table and it breaks all known rules about data modelling).

If i were "kraade", i would change the profiles table into a "clean" table with four attributes (tel, mobile, address, city) and of course with a primary key and with the foreign key to users:

Create table profiles ...

with the columns

id, user_id, tel, mobile, address, city


Or simply append these four attributes to the existing user table, as these attributes are 1:1 for each user. Makes it very easy, as only one table remains.
Nobbie
 
Posts: 9509
Joined: 09. March 2008 13:04

Re: Query pivot 2 tables

Postby Altrea » 11. February 2017 14:32

I don't know why this database structure is used. Maybe there is a cms system in the background providing the custom fields functionality, maybe the developer itself has chosen this structure.

If changing the database structure is not an option and you want to have a clean structured table more then one time without recreating the "hell of a join", than maybe a database view is an option:
Code: Select all
CREATE VIEW user_profiles_clean AS
SELECT
  `u`.`ID`,
  `u`.`name`,
  `u`.`surname`,
  `u`.`dob`,
  `up1`.`profile.value` AS `tel`,
  `up2`.`profile.value` AS `mobile`,
  `up3`.`profile.value` AS `address`,
  `up4`.`profile.value` AS `city`
FROM
  `users` AS `u`
JOIN
  `user_profiles` AS `up1`
  ON (`u`.`ID` = `up1`.`ID` AND `up1`.`profile-key` = 'profile.tel')
JOIN
  `user_profiles` AS `up2`
  ON (`u`.`ID` = `up2`.`ID` AND `up2`.`profile-key` = 'profile.mobile')
JOIN
  `user_profiles` AS `up3`
  ON (`u`.`ID` = `up3`.`ID` AND `up3`.`profile-key` = 'profile.address')
JOIN
  `user_profiles` AS `up4`
  ON (`u`.`ID` = `up4`.`ID` AND `up4`.`profile-key` = 'profile.city')
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8777
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Re: Query pivot 2 tables

Postby kraadde » 11. February 2017 15:17

@altea

Yes the reason is that a CMS system is used (JOOMLA) and that is how it is. I can not change the CMS system without creating huge problems. Therefore I will try the solution proposed and let you know if it works.
Adri
kraadde
 
Posts: 13
Joined: 10. February 2017 16:09
XAMPP version: 5.6.30
Operating System: MS Win10

Re: Query pivot 2 tables

Postby kraadde » 11. February 2017 16:36

Sorry, but it does not work with MySQLi obviously. It returns an error message 1064, i.e. not compatible code ...
Regards
Adri
kraadde
 
Posts: 13
Joined: 10. February 2017 16:09
XAMPP version: 5.6.30
Operating System: MS Win10

Re: Query pivot 2 tables

Postby Altrea » 11. February 2017 21:13

kraadde wrote:Sorry, but it does not work with MySQLi obviously. It returns an error message 1064, i.e. not compatible code ...

Sorry, but that is nonsense.
I have tested my code with phpMyAdmin. phpMyAdmin uses MySQLi too.
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 8777
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 10 Pro x64

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 43 guests