0

I'm having an issue with this SQL query, though I think it's mainly a syntax issue.

"SELECT grantYear, grantAmount AS /?\
FROM granttoassociation 
JOIN grantprovider ON granttoassociation.grantProvider  = grantprovider.id 
WHERE grantReceiver = ? "

I would like the grantAmount column to be returned under the name of the corresponding grantProvider, which can be found under grantProvider.name column in the grantprovider table.

(so in the end there would be one column returned for each different grantProvider.name)

So what should I put instead of the /?\ in my code ? grantProvider.name doesn't seem to be working.

EDIT

SELECT grantYear, grantAmount, grantprovider.name 
FROM granttoassociation JOIN grantprovider 
ON grantProvider = grantprovider.id 
WHERE grantReceiver = 2891

Result:

grantYear   grantAmount     name    
2009        3000            besancon
2010        1000            besancon
2011        0               besancon
  • The comment by @MatBailie sounds right, but could you show us some input and output so that we may be certain? – Tim Biegeleisen Jun 27 '16 at 12:28
  • 1
    You can't, not just with MySQL. The relational database structure for that would be to have 3 columns; `Provider, Year, Amount`. Then you get a new row for each `Provider` for each `Year`. If you want to *PIVOT* that to one row per `Year` then you need to know a fixed list of Providers, which doesn't fit your description. In which case you need code-that-writes-sql to generate that indeterminate number of columns. At that point it's a code-smell; you're probably aiming for the wrong thing. If this is for Presenting data, don't do it in SQL, do it in your application's presentation layer. – MatBailie Jun 27 '16 at 12:30
  • I see. I edited my question with an exemple of input output just in case. –  Jun 27 '16 at 12:35
  • There is probably just about nothing you can't [do](http://stackoverflow.com/a/38045279). The question is, how miserable of performance are you looking for. – Drew Jun 27 '16 at 14:05
  • I did as @MatBailie suggested and did the data manipulation elsewhere. –  Jun 27 '16 at 14:06

0 Answers0