php - Complex MySQL-query, union, intersect or natural join?

Answer

--------- 1 Jim 0810 2001 2 Stan 6777 2002 3 George 6532 2009 SALARY id amount year

Answer

-- 1 2000 2009 1 2500 2010 1 2800 2011 2 2100 2009 2 2200 2010 2 2500 2011 3 2200 2009 3 2300 2010 3 2800 2011 CONTACT city areacode cityname

Answer

--------- 0810 0300 Tampa 6777 0800 Miami 6532 0210 Atlanta|||name cityname hired salary

Answer

---- Jim Tampa 2001 2000 Jim Tampa 2001 2500 Jim Tampa 2001 2800 ...|||name cityname hired 2009 2010 2011

Answer

-------- Jim Tampa 2001 2000 2500 2800 ...

Answer

Solution:

You need aPIVOT query. As your tables are made up anyway I'll work off this made up table so you can see the approach.

name    cityname    year   salary
Copy code

To pivot this you would use

{-code-3}
Copy code

Answer

---- Jim Tampa 2009 2000 Jim Tampa 2010 2500 Jim Tampa 2011 2800|||SELECT name, cityname, MAX(CASE WHEN year = 2009 then salary end) AS `2009`, MAX(CASE WHEN year = 2010 then salary end) AS `2010`, MAX(CASE WHEN year = 2011 then salary end) AS `2011`, FROM T GROUP BY name, cityname

Answer

Solution:

SELECT EMPLOYES.*, GROUP_CONCAT(salery) 
FROM EMPLOYES JOIN SALERY USING(id) 
GROUP BY id
Copy code

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

But you better do it by hand, not in SQL. When I say "by hand" I mean by writing a program that does that for you. We are all programmers here, aren't we?

Answer

Solution:

As Martin commented, what youre looking for is called PIVOT in SQL Server. AFAIK MySQL doesn't support it. If you know the year range youre intrested in beforehand then you should be able to constructing the query using LEFT JOINs, something like

SELECT
  E.name,
  S2009.amount AS `2009`,
  S2010.amount AS `2010`,
  ...
FROM EMPLOYES E
LEFT JOIN SALARY S2009 ON(S2009.id = E.id)AND(S2009.year = 2009)
LEFT JOIN SALARY S2010 ON(S2010.id = E.id)AND(S2010.year = 2010)
...
Copy code