0

Note: This is not a duplicate of: mySQL - Create a New Table Using Data and Columns from Three Tables. That question is about taking data from multiple tables and actually creating a new table with that data. I'm just looking to select already existing data. The answer on that question is very specific to the question that was asked, and doesn't really help me since the code in the answer is not explained. So no, this is not "the exact same question" at all.

I have a question about joining two MySQL tables that I cannot seem to find an answer to. The usual types of joins do not seem to be what I'm after. perhaps this is not possible, I'm not sure.

So I have my first table like this (tables simplified for example purposes):

ID    Name    Email               Phone
1     John    john@example.com    000-123-4567
2     Jane    jane@example.com    000-890-1234

My second table is arranged this way:

ID   UserID    meta_key            meta_value
1    1         location_review     4
2    1         condition_review    1
3    1         price_review        5
4    2         location_review     4
5    2         condition_review    2
6    2         price_review        4

I am working with some existing software, just making some modifications, so ideally I can do what I need without needing to change how the software is written too much.

I am trying to be able to join these tables so they look like this:

    ID    Name    Email               Phone          location_review    condition_review    price_review
    1     John    john@example.com    000-123-4567   4                  1                   5
    2     Jane    jane@example.com    000-890-1234   4                  2                   4

So I need to add the rows from Table2 that have the UserID to the data already in Table1.

Since this is existing software there is already some code in place to join these two tables, it is here:

SELECT * 
FROM wpnf_comments as comment 
    INNER JOIN wpnf_commentmeta AS meta 
WHERE comment.comment_post_ID = $prop_ID 
AND meta.meta_key = 'rating' 
AND meta.comment_id = comment.comment_ID 
AND ( comment.comment_approved = 1 
    OR comment.user_id = $userID 
    )

What I am doing is: There used to be only a single "rating" value, stored in the meta table. You can see it in the above Join where it says:

AND meta.meta_key = 'rating'

However, I am trying to replace that with 3 separate ratings. So the join above, that used to work when there was only a single rating, doesn't really work when I have multiple ratings.

I would very much appreciate any insight into what I need to do to fix this. I am also using PHP for this, if that matters.

EDIT

This is the code I have so far. it's showing all of the correct column names I need now, but the actual values/data is not showing up. It's returning 0 rows.

SELECT 
  * 
FROM 
  wpnf_comments t1 
  INNER JOIN 
  (SELECT 
      comment_id as comment_id2, 
      MAX(IF(meta_key = 'location-rating', meta_value, NULL)) AS location_rating, 
      MAX(IF(meta_key = 'condition-rating', meta_value, NULL)) AS condition_rating, 
      MAX(IF(meta_key = 'maintenance-rating', meta_value, NULL)) AS maintenance_rating, 
      MAX(IF(meta_key = 'professionalism-rating', meta_value, NULL)) AS professionalism_rating, 
      MAX(IF(meta_key = 'contact-rating', meta_value, NULL)) AS contact_rating, 
      MAX(IF(meta_key = 'availability-rating', meta_value, NULL)) AS availability_rating, 
      MAX(IF(meta_key = 'responsiveness-rating', meta_value, NULL)) AS responsiveness_rating, 
      MAX(IF(meta_key = 'price-rating', meta_value, NULL)) AS price_rating 
   FROM 
      wpnf_commentmeta 
   GROUP BY comment_id) temp_t 
      ON t1.comment_post_ID = temp_t.comment_id2
Sherwin Flight
  • 2,345
  • 7
  • 34
  • 54
  • Can someone please help me with this? I've spent at least two hours on this now with no luck. – Sherwin Flight Jul 18 '17 at 09:07
  • It would be better if you used [normalization](https://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization) on your relational database due to the re-occurring meta_key values. This would mean you would need a third table for your data. Able to create a third table or are you restricted to these two tables only? – MinistryOfChaps Jul 18 '17 at 09:08
  • @MinistryofChaps the software is going to put data into these two existing tables, unless I re-code quite a bit of it. So ideally I'd like to just use these two, otherwise I'd be constantly needing to update the third table with the data from these two tables. – Sherwin Flight Jul 18 '17 at 09:10
  • @MinistryofChaps and thanks for any help you can provide. I've spent way longer on this than I had anticipated. – Sherwin Flight Jul 18 '17 at 09:11
  • Personally I'd recommend re-coding to accommodate normalization (especially if your database is going to be huge) but I'll keep looking into a solution for your two tables because I understand time constraints for re-coding can be an issue. – MinistryOfChaps Jul 18 '17 at 09:13
  • @MinistryofChaps it's WordPress. So re-coding is not really an option. Comments are in one table, comment "meta" is in another. I'm just using some custom post types and whatnot to make it behave differently than normally. So since this is just how WordPress stores the data re-coding would be hard. I can probably do something hacky, and use some PHP code to try to add the values on to the end, but that really isn't the best solution. – Sherwin Flight Jul 18 '17 at 09:16
  • @MinistryofChaps on the answer here, https://stackoverflow.com/questions/26665499/mysql-create-a-new-table-using-data-and-columns-from-three-tables, what does the ON/AS statements mean in the third code block? – Sherwin Flight Jul 18 '17 at 09:16
  • I'm not sure at the moment, I have to attend a matter and I've got this partially working query based of [this question](https://stackoverflow.com/questions/12808189/setting-column-values-as-column-names-in-the-sql-query-result). I'm sorry to leave you but a matter has come up, if you don't find a solution in the meantime I will come back to you. – MinistryOfChaps Jul 18 '17 at 09:44
  • Here is the partially working code: `SELECT u.ID, u.Name, u.Email, u.Phone, MAX(CASE WHEN (m.meta_key = 'location_view') THEN m.meta_value ELSE NULL END) AS location_view, MAX(CASE WHEN (m.meta_key = 'condition_review') THEN m.meta_value ELSE NULL END) AS condition_review, MAX(CASE WHEN (m.meta_key = 'price_review') THEN m.meta_value ELSE NULL END) AS price_review FROM wpnf_comment u, wpnf_commentmeta m WHERE u.ID = m.UserID GROUP BY u.ID ORDER BY u.ID` – MinistryOfChaps Jul 18 '17 at 09:45
  • I f I understand correctly, so far there is up to one location_review, one condition_review, and one price_review per user. But you want to allow for multiple entries. Then what do you want to show in your results, if there are two reviews for a user? The minimum, value, the maximum value, the sum, the latest entry? – Thorsten Kettner Jul 18 '17 at 09:58
  • @ThorstenKettner there will only ever be a single entry per "type" of review/rating. So, for example, user #111 will only have one meta entry for "location_review", one for "price_review", etc. It's just that the meta values are not stored the same way as the main content I'm linking it to. – Sherwin Flight Jul 18 '17 at 10:01
  • Basically each "review" is a single entry in one DB table, but each review can have multiple ratings (location, price, condition, etc) that are stored in a second DB table. So I'm trying to link multiple rows of table2, based on a "review_id" field, with a single row in table one. So essentially taking the vertical data from multiple rows in table two and placing it horizontally alongside the matching data from table 1. – Sherwin Flight Jul 18 '17 at 10:06
  • Glad you got a solution @SherwinFlight, best of luck with the rest. – MinistryOfChaps Jul 18 '17 at 12:13

1 Answers1

2

Using MySQL pivot tables

SELECT 
      temp_t.user_id, t1.Name, t1.Email, t1.Phone, temp_t.location_review, temp_t.condition_review, temp_t.price_review 
    FROM 
      table_1st t1 
      INNER JOIN 
      (SELECT 
          UserID as user_id, 
          MAX(IF(meta_key = 'location_review', meta_value, NULL)) AS location_review, 
          MAX(IF(meta_key = 'condition_review', meta_value, NULL)) AS condition_review, 
          MAX(IF(meta_key = 'price_review', meta_value, NULL)) AS price_review 
       FROM 
          table_2nd 
       GROUP BY UserID) temp_t 
          ON t1.ID = temp_t.user_id
Tanmay Maity
  • 86
  • 6
  • Is there a way to select * like this, or do I need to specify each field? – Sherwin Flight Jul 18 '17 at 09:56
  • You can use SELECT * on the outer SELECT, this will give the all columns from table_1st and the 4 columns from the INNER SELECT query. But then user_id will be twice. – Tanmay Maity Jul 18 '17 at 10:18
  • I'm getting very close with this code. In phpmyadmin when I run the code I have the correct col names I want are showing up now :) However the values are not, it's returning 0 rows. So it's a step in the right direction, as I now have the cols I need, I just need to get the data/values to show up. – Sherwin Flight Jul 18 '17 at 10:20
  • I added an edit to the bottom of my question above, showing the code I have so far based on your example. – Sherwin Flight Jul 18 '17 at 10:22
  • I also need to be able to specify the post ID that is to be queried. – Sherwin Flight Jul 18 '17 at 10:25
  • How can I add a "WHERE" statement to the outer select? – Sherwin Flight Jul 18 '17 at 10:25
  • I think i got it. There was a spelling mistake in my code. – Sherwin Flight Jul 18 '17 at 10:29
  • The code I ended up using was just a slight modification of the code you posted above. Since you were the one that pointed me in the right direction, and helped me solve this problem, I have no problem upvoting your answer and marking it as the accepted answer. Thanks for your help :) – Sherwin Flight Jul 18 '17 at 10:42
  • @SherwinFlight what was the query that worked for you to show the values? Some values are not appearing for myself when I tried testing this. [screenshot](https://i.gyazo.com/2853729781c870ea9b75ab19e9ae0cec.png) – MinistryOfChaps Jul 18 '17 at 18:25