0

I have table names 'payroll' with following data

month , pay
January , 1200
March , 1500
December , 2000


I want the following result

Janury , 1200
February , 00
March , 1500
April , 00
May , 00
June , 00
July , 00
August , 00
September , 00
October , 00
November , 00
December , 00


Please help to make the query.

Thanks in advance

5
Contributors
7
Replies
16
Views
6 Years
Discussion Span
Last Post by drjohn
0

nearly right

it's a left outer join

SELECT months.month, IFNULL( payroll.pay, 0 ) AS pay
FROM months
LEFT OUTER JOIN payroll ON payroll.month = months.month

Edited by drjohn: n/a

0

Please explain the difference between LEFT JOIN and LEFT OUTER JOIN in MySQL. To the best of my knowledge there isn't any.

0

Please explain the difference between LEFT JOIN and LEFT OUTER JOIN in MySQL. To the best of my knowledge there isn't any.

I can't find a clear explanation to this in docs but this question was asked on StackOverflow and the accepted answer was that it makes no difference which you use.

At another site someone wrote the following: "MySql only supports the LEFT OUTER JOIN syntax so as to support ODBC compliance."

2

You could use the following query as an alternative to creating a table of months.

SELECT m.month, p.pay
FROM (
SELECT 'January' AS
MONTH
UNION SELECT 'February' AS
MONTH
UNION SELECT 'March' AS
MONTH
UNION SELECT 'April' AS
MONTH
UNION SELECT 'May' AS
MONTH
UNION SELECT 'June' AS
MONTH
UNION SELECT 'July' AS
MONTH
UNION SELECT 'August' AS
MONTH
UNION SELECT 'September' AS
MONTH
UNION SELECT 'October' AS
MONTH
UNION SELECT 'November' AS
MONTH
UNION SELECT 'December' AS
MONTH
) AS m
LEFT JOIN payroll p ON m.month = p.month
Votes + Comments
What a nice trick!
0

Please explain the difference between LEFT JOIN and LEFT OUTER JOIN in MySQL. To the best of my knowledge there isn't any.

Sorry, my mistake. I just had it drummed into me to say left outer join and forgot that left join was identical.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.