CODEWITHSUNDEEP
phpmysqldatabase
Marcus Olsson
Marcus Olsson

Reputation: 2527

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

Having an odd problem with a MySQL query, I can't really figure out how to get the data organized as I wish.

I'm building search-script in PHP, but the database structure isn't in the way that I would like.

Okay, say that I have three tables (these are completely made up):

EMPLOYES
id      name        city        hired
-------------------------------------------------
1       Jim         0810        2001
2       Stan        6777        2002
3       George      6532        2009

SALARY
id      amount      year
--------------------------------
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
-----------------------------
0810    0300        Tampa
6777    0800        Miami
6532    0210        Atlanta

Now, if I stick to LEFT JOINs etc. I am able to get a result that look like this, where it repeats every row in in the Salary table:

name    cityname    hired   salary
----------------------------------
Jim     Tampa       2001    2000
Jim     Tampa       2001    2500
Jim     Tampa       2001    2800
...

But what I really want is something like this:

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

Any way that I can achieve this with SQL?

Upvotes: 1

Views: 635

Answers (3)

ain
ain

Reputation: 22749

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)
...

Upvotes: 0

sanmai
sanmai

Reputation: 30881

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

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?

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453278

You need a PIVOT 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
----------------------------------
Jim     Tampa       2009    2000
Jim     Tampa       2010    2500
Jim     Tampa       2011    2800

To pivot this you would use

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

Upvotes: 1

Related Questions