Help on an SQL Query

[:: Lyon ::]

Say I have 2 tables:

Table Employee

=================

| id | firstname | surname |

--------------------------------

| 1 | John | Smith |

--------------------------------

Table Allowances

=============================

| employeeid | type | amount

-----------------------------------------------------

| 1 | communication | 100

| 1 | car | 1500

------------------------------------------------------

My current SQL is

SELECT e.id, e.firstname, a.employeeid, a.type, a.amount

FROM employee e, allowances a

WHERE e.id = a.employeeid

This will gives me some rows of "semi-duplicates" like:

Row 1: 1, John, Smith, communication, 100

Row 2: 1, John, Smith, car, 1500

Would it be possible to have an SQL query where the result is like:

|id|firstname|surname|communication_allowance|car_allowance|

1, John, Smith, 100, 1500

[Express]

you need to use a subquery.

Something like:

Select id, firstname, lastname, (select amount from Allowances where

employeeid =id and type='communication') as communication_allowance,

(select amount from Allowances where

employeeid =id and type='car') as car_allowance

FROM employee

Just realized yours was a MySQL question.

I don't know MySQL -- only MS SQL Server.

So this might not work.

[yamashita]

  :: Lyon :: said:

Say I have 2 tables:

Table Employee

=================

| id | firstname | surname |

--------------------------------

| 1 | John | Smith |

--------------------------------

Table Allowances

=============================

| employeeid | type | amount

-----------------------------------------------------

| 1 | communication | 100

| 1 | car | 1500

------------------------------------------------------

My current SQL is

SELECT e.id, e.firstname, a.employeeid, a.type, a.amount

FROM employee e, allowances a

WHERE e.id = a.employeeid

This will gives me some rows of "semi-duplicates" like:

Row 1: 1, John, Smith, communication, 100

Row 2: 1, John, Smith, car, 1500

Would it be possible to have an SQL query where the result is like:

|id|firstname|surname|communication_allowance|car_allowance|

1, John, Smith, 100, 1500

I not sure whether this works, I'm trained on Oracle SQL though I used mySQL a number of times.

SELECT e.id, e.firstname,

e.surname,

(select a.amount from allowances a where a.employeeid = e.id and a.type = 'communication' and rownum = 1) "communication_allowance",

(select a.amount from allowances a where a.employeeid = e.id and a.type = 'car' and rownum = 1) "car_allowance"

from employee e

Note that this works only when there is only 1 row per allowance type, per employee id in allowances. (Hence the rownum = 1).

[:: Lyon ::]

I'm about to try both of your suggestions, and find out that we're still using MySQL 4.0.19 in our server lol

I need to update it first as it doesn't seem to support subQuery yet (based on Googling)

[jamend]

It depends. If you already know the employee types are constant then it's pretty easy with the subquery methods mentioned before, but it also means that your data model is flawed... But if the employee types are not constant, then the subquery method won't work since it only looks for "car" and "communication". Any other types wouldn't show up. It's still possible, but I only know of a way to do it in MS SQL 2005 with the PIVOT option. I don't know if Oracle or MySQL have a similar feature. Basically it just makes the rows the columns and the columns the rows.