0

silly question, how do i join 2 tables like this

table1
-------------
id  pagetitle
1   x
2   y
3   z
-------------


table2
------------
id   contentid      value
1     1             img.jpg
2     1             someval
3     1             someval2
4     2             x
5     3             y


RESULTS SHOULD BE ONE BIG TABLE
-----------------------------------------------------------------------------
id    pagetitle  id(idfrom table2...)  contentid   value1    value2   value3
1     x             1                     1        img.jpg   someval  someval2
2     y             4                     2        x
3     z             5                     3        y
-----------------------------------------------------------------------------

interested in getting all 3 or more values for each row, no duplicates

I TRIED This portion of code in my query(example), but i get duplicated row for each value found in the table2:

SELECT * FROM `table1` AS t1 
           INNER JOIN `table2` AS t2
               ON t1.id = t2.contentid

thanks

Jaciel Lv
  • 33
  • 1
  • 6
  • >I TRIED This portion of code in my query(example), but i get duplicated row for each value found in the table2 - can you add a `DISTINCT` ? – Strikegently Sep 22 '17 at 15:55
  • 1
    Check out [MySQL pivot table query with dynamic columns](https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) What are you trying to do is called a "Dynamic Pivot". – JNevill Sep 22 '17 at 16:00
  • This is quite difficult question to answer. Up to how many values you can have per contentid? Can you somehow introduce a 4th column in the table2 that could represent the number of column of the value (eg. if it is the 1st value, the 2nd or the 3rd)? – Efthymios Kalyviotis Sep 22 '17 at 16:35
  • Presentation issues are usually handled in the presentation layer. If you MUST get the data out of the system formatted this way, then check out @JNevill 's link – xQbert Sep 22 '17 at 17:02

2 Answers2

1

It is quite difficult answer but if you can have a single column with all values separated with comma, then the following SQL statement should do for you:

SELECT t1.id, t1.pagetitle, t2.contentid, GROUP_CONCAT(value SEPARATOR ', ') as val
FROM table1 AS t1 
    INNER JOIN table2 AS t2 ON t1.id = t2.contentid
GROUP BY t1.id, t1.pagetitle, t2.contentid

this will result to something like the bellow:

  id, pagetitle, contentid, val
  1 , x ,        1 ,        someval2, img, someval 
  2 , y ,        2 ,        x 
  3 , z ,        3 ,        y
Efthymios Kalyviotis
  • 859
  • 10
  • 24
-1

SELECT * FROM table1 AS t1 OUTER JOIN table2 AS t2 ON t1.id = t2.contentid

hudson1995
  • 19
  • 2
  • This does not result in the return that OP is looking for. Furthermore, some explanation is expected besides a single line of code for an answer. Lastly, I am no MySQL expert, but "OUTER JOIN" is not supported. Lastly lastly, you can add four spaces before lines containing code to have it formatted as such (or highlight the code and hit the code button or Ctrl+k) – JNevill Sep 22 '17 at 17:05