MySQL JOIN Statement from Multiple Tables

Question

I have an old database of entries from an abandoned "Joomgalaxy" Joomla plugin.

There are three tables, joomgalaxy_entries, joomgalaxy_fields, and joomgalaxy_entries_data

The id from the entries table matches the entry_id in the entries_data table, but the actual field name is saved in another table, fields

Can someone please help me with the correct SQL statement to obtain results like you can see below in Ultimate Goal? My MySQL knowledge is very basic, and from my searching it sounds like I need to use a LEFT JOIN, but I have no idea how to use the value from field_name as the column name for returned values

Thank You!!

joomgalaxy_entries

---------------------------------------
| id | title | longitude | latitude   |
---------------------------------------
| 50  | John | -79.333333 | 43.669999 |
| 51  | Bob  | -79.333333 | 43.669999 |
---------------------------------------

joomgalaxy_fields

This is just two examples below to keep it simple, there are more than just these two, so it would have to be able to handle dynamically using the field_name as the column name.

--------------------------------
| id | field_type | field_name |
--------------------------------
| 1  | textbox    | websiteurl |
| 2  | dropdown   | occupation |
--------------------------------

joomgalaxy_entries_data

"Technically" there shouldn't be any duplicate entries (fieldid and entry_id), so from my understanding that shouldn't affect using the field_name from above as the column name, but what if there ends up being one?

-------------------------------------
| fieldid | field_value | entry_id |
-------------------------------------
|   1     | google.com  |    50    | 
|   2     | unemployed  |    50    |
|   1     | doctor.com  |    51    | 
|   2     | doctor      |    51    |
-------------------------------------

Ultimate Goal

Ultimately trying to get this type of result, so I can then use that statement in MySQL Workbench to export the data that would look like this:

------------------------------------------------------------------
| id | title | longitude  | latitude   | websiteurl | occupation |
------------------------------------------------------------------
| 50  | John | -79.333333 | 43.669999  | google.com | unemployed |
| 51  | Bob  | -79.333333 | 43.669999  | doctor.com | doctor     |
------------------------------------------------------------------

EDIT: There are more than just the two fields websiteurl and occupation, I was just using those two as examples, there are numerous fields that are all different, so in theory pulling the value from field_name would be used for the column name

Answer 1

You can use some conditional logic, like a CASE statement, along with an aggregate function like max() or min() to return those values as columns:

SELECT je.id,
  je.title, 
  je.longitude,
  je.latitude,
  max(case when jf.fieldid = 1 then jed.field_value end) as WebsiteUrl,
  max(case when jf.fieldid = 2 then jed.field_value end) as Occupation
FROM  joomgalaxy_entries je
INNER JOIN joomgalaxy_entries_data jed
  on je.id = jed.entry_id
GROUP BY je.id,
  je.title, 
  je.longitude,
  je.latitude

Using an INNER JOIN will only return the joomgalaxy_entries rows that have values in each table, if you want to return all joomgalaxy_entries even if there are no matching rows to join on in the other tables, then change the INNER JOIN to a LEFT JOIN.

Answer 2

You can write a simple SELECT query like this:

SELECT je.id, je.title, je.longitude, je.latitude, 
(SELECT field_value FROM joomgalaxy_entries_data WHERE fieldid = 1 AND entry_id = je.id) AS websiteurl,
(SELECT field_value FROM joomgalaxy_entries_data WHERE fieldid = 2 AND entry_id = je.id) AS occupation
FROM joomgalaxy_entries je;

Answer 3

First step is easy:

SELECT JE.id, JE.title, JE.longitude, JE.latitude
FROM joomgalaxy_entries JE

Now you need to JOIN:

SELECT JE.id, JE.title, JE.longitude, JE.latitude,
       JD.*
FROM joomgalaxy_entries JE
JOIN joomgalaxy_entries_data JD
  ON JE.id = JD.entry_id

Now you need convert rows to columns

SELECT JE.id, JE.title, JE.longitude, JE.latitude,
       MIN(CASE WHEN fieldid = 1 THEN JD.field_value END) as WebsiteUrl,
       MIN(CASE WHEN fieldid = 2 THEN JD.field_value END) as Occupation
FROM joomgalaxy_entries JE
JOIN joomgalaxy_entries_data JD
  ON JE.id = JD.entry_id
GROUP BY JE.id, JE.title, JE.longitude, JE.latitude

This depend on you only have two field for each entry, if number of field is dynamic you would need a different aproach.

Answer 4

This should work:

select id, title, longitude, latitude,
    (select field_value from joomgalaxy_entries_data jed
      where fieldid = (select id from joomgalaxy_fields
                        where field_name = 'websiteurl')
        and jed.entry_id = je.id
       ) as websiteurl,
    (select field_value from joomgalaxy_entries_data jed
      where fieldid = (select id from joomlgalaxy_fields
                        where field_name = 'occupation')
        and jed.entry_id = je.id) as occupation
  from joomgalaxy_entries je;

Note that the reason to have a left join would be if either websiteurl or occupation were null, however, this solution should work in that case anyway.

Answer 5

Well, that certainly makes it a bit more difficult... :) Honestly, I'm not sure what you're asking is possible with a static sql query. I'm sure someone will speak up, however, if I'm wrong.

That said, I do have a few options you can try:

Option 1 - Generate the SQL Dynamically

Assuming this is mysql, if you execute the following SQL, it will generate the subqueries dynamically:

select concat('(select field_value from joomgalaxy_entries_data jed ',
              'where fieldid = (select id from joomgalaxy_fields ',
              'where field_name = ''', field_name, ''') ',
              'and jed.entry_id = je.id) as ', field_name, ',')
              from joomgalaxy_fields;

Take the result of that command, copy-paste it into a text editor and add the following at the beginning:

select id, title, longitude, latitude,

And the rest of this at the end:

from joomgalaxy_entries je;

Then run your new uber-query and go grab a cup of copy, lunch, or a good night's sleep depending on how much data is in your database.

Alternatively, you could add all of this to a stored procedure so you don't have to hand edit the SQL. Also, note that my syntax works for MySQL. Other databases have different concatenation operators so you may have to work around that if applicable. Also, with 50+ subqueries there is a good chance this uber-query will be quite slow, maybe too slow to make this option viable.

Option 2 - Create a table structured the way you want, and populate it

Hopefully, this is self-explanatory, but just create a new table with all of the necessary columns from the joomgalaxy_fields table. Then populate each column separately with a long series of what should be pretty straightforward sql commands. Granted this option is only viable if the database is no longer in use which I believe you indicated. From there the result is just:

select * from my_new_table;