Empty strings in sql

m having 2 tables

开发者_StackOverflow1) HR_PAY_EMPLOYEE_PAYSLIP_HISTORY_DETAILS

2)HR_PAY_EMPLOYEE_PAYSLIP_HISTORY

table 1 has PAY_HEAD_ID,PAYSLIP_HISTORY_ID and VALUE as its columns

table 2 has EMPLOYEE_ID,PAYSLIP_HISTORY_ID as its columns

I map table 1 and table 2 on PAYSLIP_HISTORY_ID to retrieve the EMPLOYEE_ID,PAY_HEAD_ID,VALUE

The problem is, all employees do not have all PAY_HEAD_IDs and the VALUEs

How can i retrieve all the PAY_HEAD_IDs of all employees and their values(0.00 incase the value is not stored) ??

This may vary slightly depending on what dialect of SQL you're using, but you'll need to use a left outer join, something like this:

SELECT
    EMPLOYEE_ID,
    PAY_HEAD_ID,
    isnull(VALUE, 0.00) as VALUE
FROM
    HR_PAY_EMPLOYEE_PAYSLIP_HISTORY HISTORY
    LEFT OUTER JOIN HR_PAY_EMPLOYEE_PAYSLIP_HISTORY_DETAILS DETAILS
    ON HISTORY.PAYSLIP_HISTORY_ID = DETAILS.PAYSLIP_HISTORY_ID

It depends on which database software you are using, but most have an NVL() function that allows you to change the value of NULL. If it's an empty string rather than null, use a DECODE() (or equivalent) statement to change its value.