0

I've a table called "messages" and another one called "votations". Each message can have more than one vote, The problem that actually they are placed in different rows.How can i Fix it?

The actual query is: 

SELECT messages.ID, votations.value
FROM messages 
LEFT JOIN votations ON messages.ID=votations.messageID

enter image description here

Ajith
  • 775
  • 1
  • 13
  • 47
user2590550
  • 11
  • 5
  • Funny, you did it the right way and now you want the wrong way solution. Why? What do you get if you have votes displayed as columns? What's wrong with how it works currently? – N.B. Jul 17 '13 at 09:12

1 Answers1

3

Unfortunately you cannot do this purely in MySQL. In the result set you can't have rows with different (number of) columns. You need some application logic implemented. The closest you can get to this is:

select m.id, GROUP_CONCAT(voto SEPARATOR ',') 
from messages m 
left join votations v on v.messageID = m.id
group by m.id;

This will return this format:

ID  GROUP_CONCAT(VOTO SEPARATOR ',')
1   2,5,6
2   2,12,3

You can see this working here: http://sqlfiddle.com/#!2/241a8/3

Károly Nagy
  • 1,734
  • 10
  • 13
  • It's ok, but won't work in my project.. I've this result on 69 ID: 69 [BLOB - 5 B] – user2590550 Jul 17 '13 at 09:12
  • In which case try casting voto to a different (string) datatype before concatenating it (what data type is voto currently). – Kickstart Jul 17 '13 at 09:20
  • Did you try this in phpmyadmin? Check out the following question: http://stackoverflow.com/questions/2188264/viewing-content-of-blob-in-phpmyadmin – Károly Nagy Jul 17 '13 at 09:20
  • Actually voto is decimal(10,0) type... So i have to set it as TEXT? – user2590550 Jul 17 '13 at 09:24
  • GROUP_CONCAT is to concatenate strings together. I suspect that MySQL has defaulted to converting the field to a blob before concatenating them. Leave the field as a decimal, but do the CAST within the GROUP_CONCAT. – Kickstart Jul 17 '13 at 09:48
  • Why is it decimal if you don't expect to have decimals? Wouldn't it be easier to use tinyint (only 1 byte)? – Károly Nagy Jul 17 '13 at 10:23
  • By the way. MySQL does every casting necessary internally for you. You don't need to do anything. BLOB - 5B is just the representation of the value. Probably if you do it in your application you won't experience the same and everything will be just fine. (The mysql connectors take care of MySQL type to variable type casting too). – Károly Nagy Jul 17 '13 at 10:27
  • anyway my objective is to calculate the average valutation (sum of valutation/total valutations). Is it possibile thrrought the same query or i've to do it manually with successive php scripting? – user2590550 Jul 17 '13 at 19:42
  • Yes, it's possible. The query for the averages would be: select m.id, AVG(voto) as average_valuation from messages m left join votations v on v.messageID = m.id group by m.id; – Károly Nagy Jul 19 '13 at 10:47