1

I have a question concerning MySQL, I have 2 tables:

Data:

 ID ..  |  name ..  |   Job...|
 001    |  name1.   |   Job1  |
 002    |  name2.   |   Job2  |
 003    |  name3.   |   Job3  |

dataAttributes :

 ID...  |   Number |
 001    |  value 1 .|
 001    |  value 2 .|
 002    |  value 3 .|
 003    |  value 4 .|
 003    |  value 5 .|

I want to join these tables in a way to have:

 ID ..  |   name.. |   Job ..|Number 1     | Number 2..|
 001    |  name1   |   Job1  |value 1......| value 2 ..|
 002    |  name2   |   Job2  |value 3......|...........|
 003    |  name3   |   Job3  | value 4.... | value 5 ..|

Can you please give me any help ?

 =======Solved=======

I have found the solution myself by using the following query

 select 
 D.*,
 (select DA.number from dataAttributes as DA where D.ID = DA.ID LIMIT 1,1) as number1,
 (select DA.number from dataAttributes as DA where D.ID = DA.ID LIMIT 2,1) as number2,
 (select DA.number from dataAttributes as DA where D.ID = DA.ID LIMIT X,1) as numberX
 from data as D
user3801843
  • 11
  • 2
  • 1
    possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) – FuzzyTree Nov 11 '14 at 23:23
  • Thnks for your reply. I have read that post before asking this question but it isdifferent than my case. Since I have multiple attribut for the same id – user3801843 Nov 12 '14 at 17:14

2 Answers2

1

Try this

SELECT * FROM data
INNER JOIN dataAttributes ON data.ID = DataAttributes.ID;
Henrik R
  • 4,742
  • 1
  • 24
  • 23
  • Thnks for replying. However this query will only join both tables without giving me the attribut in the column view. – user3801843 Nov 12 '14 at 17:11
0

If you only have two numbers, you can use min() and max():

select d.*, min(da.Number) as Number1, max(da.Number) as Number2
from data d left join
     dataAttributes da
     on d.id = da.id
group by d.id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thnks for your reply. Actually I have more than two values per Id. But this will help me in another query – user3801843 Nov 12 '14 at 17:12
  • @user3801843 . . . I would suggest that you ask *another* question, with more detail about what you are really trying to accomplish. Include such information about how you decide which number goes in which row. Just editing this question would be impolite to those who have answered, because changing the question can invalidate the answers attracting down votes. – Gordon Linoff Nov 12 '14 at 20:19
  • No, I didn't change the question, I just answered yours when you said that your query will work for two numbers, this query will help me with another issue, but the initial question remains unanswered :) – user3801843 Nov 12 '14 at 22:05
  • @user3801843 . . . Your question has two numbers in it. This answer answers that question. You should ask another question clarifying what you seem to really need. – Gordon Linoff Nov 12 '14 at 22:45
  • thnks again, I have found a solution to my problem, you can check it if you like in the original post – user3801843 Nov 12 '14 at 23:02
  • @user3801843 . . . You are using `limit` without an `order by`. That means that the results are not guaranteed to be correct. – Gordon Linoff Nov 13 '14 at 00:08
  • Thnks for your comment, I will take it in consideration. – user3801843 Nov 13 '14 at 11:06