0

I want to ask to join 2 tables. I have first table like this :

|idsite  | nmsite  |
-------------------
|   1    |   nm1   |
|   2    |   nm2   |
|   3    |   nm3   |
--------------------

and the second table is :

|idsite  |  iddetsite |  dataA  | dataB  | dataC  | time       |
----------------------------------------------------------------
|  1     |     1      |    3    |    4   |   5    |  2015-01-01|
|  1     |     2      |    3    |    2   |   3    |  2015-02-01|
|  1     |     3      |    5    |    3   |   2    |  2016-01-01|
|  1     |     4      |    5    |    3   |   2    |  2016-02-01|
|  2     |     5      |    2    |    5   |   3    |  2015-02-02|
|  2     |     6      |    5    |    3   |   2    |  2016-04-02|
|  3     |     7      |    7    |    1   |   1    |  2015-01-01|
|  3     |     8      |    3    |    3   |   3    |  2016-02-02|
----------------------------------------------------------------

The result for dataA should be:

|   time    | nm1   |  nm2  |  nm3 |
------------------------------------
|   2015    |   6   |   2   |   7  |
|   2016    |   10  |   5   |   3  |
------------------------------------

ket: nm1 in the result table is sum(dataA) by year, anyone can help me?, it is different question, I ask in third table or the result table, there are 4 columns, times from 2 table, and nm1,nm2,nm3 from 1 table field, so the third table is the result where the data field from 1 table will be the header column in 3 table(result table), i think this is the different kind. Thanks

Mike
  • 11
  • 4
  • @Mike Can you share your table and rows using http://sqlfiddle.com/ ? – nice_dev Jun 15 '18 at 09:06
  • You have no clue how to do that? Not even a basic approach? – CannedMoose Jun 15 '18 at 09:07
  • Are datas a, b, c the same kind of thing? Could there be a dataD, and would it also be the same kind of thing? – Strawberry Jun 15 '18 at 09:07
  • @user1055395 : no mate, nm2 is data in first table (nmsite). – Mike Jun 15 '18 at 09:36
  • This transformation is called pivoting and has been asked and answered here on sooooo many times. Pls note that the linked duplicate topic links show you how to do this transformation within MySQL,h owever, it may be more efficient to do this in the application logic. The 2nd duplicate link contains a very detailed, step-by-step explanation as to how to carry out such transformations within MySQL. – Shadow Jun 15 '18 at 09:39
  • @CannedMoose : I know mate for create table, join table, union, but I never found the result like this, so I just want to find out mysql code for the result table! – Mike Jun 15 '18 at 09:40
  • @Shadow : thank you very much for your information, but sorry I could not find any information related to my question before....you mention if my question has been answered here so many times can you give the link of the answer or some information related to where I can find the solution for my problem..more specifically? – Mike Jun 15 '18 at 09:45
  • I already provided 2 links - they are visible on the top of the page. – Shadow Jun 15 '18 at 09:50
  • @Shadow : thank you for sharing the link, but actually, it's not what I was asking about....I have made the first and second table, and my question is related to the third table. for what I know, third table is the result of joining first and second table...but I don't know how to make the coding for the third table...so my main problem is I do not know how to join first and second table... – Mike Jun 15 '18 at 09:58
  • There are hundreds of questions on SO that show you how to join 2 tables in mysql. It is pretty hard to believe that you did not find any questions that described how two tables are joined. – Shadow Jun 15 '18 at 10:20

0 Answers0