CODEWITHSUNDEEP
sqlsql-serverjoin
Joe Yan
Joe Yan

Reputation: 2095

SQL Server Join Table Statement

Can I join the following 3 statement to display sick_total_day, other_total_day, other_total_day in one row (same where clause) ?

SELECT staff_key, 
       sum(days_applied) as sick_total_day 

from Tleave_sick_leave 

where staff_key = 131 
and from_date>='2011/4/1 00:00:00' 
and to_date<='2011/4/30 00:00:00';


SELECT staff_key, 
       sum(days_applied) as other_total_day 
from Tleave_other_leave 
where staff_key = 131 
and from_date>='2011/4/1 00:00:00' 
and to_date<='2011/4/30 00:00:00';


SELECT staff_key, 
       sum(days_applied) as other_total_day 

from Tleave_vleave 
where staff_key = 131 
and from_date>='2011/4/1 00:00:00' 
and to_date<='2011/4/30 00:00:00';

Upvotes: 4

Views: 158

Answers (4)

Joe Yan
Joe Yan

Reputation: 2095

Little bit amendment for the code provided by Lieven, it works now...

SELECT  a.staff_key
        , SUM(CASE WHEN a.type = 'Sick' THEN 
                        a.days_applied ELSE 0 END) as sick_total_day       
        , SUM(CASE WHEN a.type = 'Othert' THEN
                        a.days_applied ELSE 0 END) as othert_total_day       
        , SUM(CASE WHEN a.type = 'Otherv' THEN 
                        a.days_applied ELSE 0 END) as otherv_total_day       
FROM    
(
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    , type = 'Sick'
            FROM    Tleave_sick_leave
            UNION ALL
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    ,type = 'Othert'
            FROM    Tleave_other_leave
            UNION ALL
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    ,type = 'Otherv'
            FROM    Tleave_vleave
) a
WHERE staff_key = '131' AND 
      from_date>='2011/4/1 00:00:00' AND 
      to_date<='2011/4/30 00:00:00'
GROUP BY staff_key

Upvotes: 0

Robin Day
Robin Day

Reputation: 102548

You can UNION your three tables together to make one set of data. Then you can use PIVOT to transpose the SUM into columns.

This should work. Although I have not tested it with any data.

SELECT
    [Tleave_sick_leave],
    [Tleave_other_leave],
    [Tleave_vleave]
FROM
(
    SELECT
        'Tleave_sick_leave' [Table],
        [staff_key],
        [days_applied],
        [from_date],
        [to_date]
    FROM
        [Tleave_sick_leave]
    UNION ALL
    SELECT
        'Tleave_other_leave' [Table],
        [staff_key],
        [days_applied],
        [from_date],
        [to_date]
    FROM
        [Tleave_other_leave]
    UNION ALL
    SELECT
        'Tleave_vleave' [Table],
        [staff_key],
        [days_applied],
        [from_date],
        [to_date]
    FROM
        [Tleave_vleave]
) [PivotData]
PIVOT
(
    SUM([days_applied])
FOR
    [Table]
IN
    (
        [Tleave_sick_leave],
        [Tleave_other_leave],
        [Tleave_vleave]
    )
) [Data]
WHERE
    [staff_key] = 131
AND
    [from_date] >= '2011/4/1 00:00:00' 
AND
    [to_date] <= '2011/4/30 00:00:00'

NOTE: This is a SQL Server 2005 up approach. If you have 2000 or lower then you will need to go with Lieven's answer which effectively does the same thing.

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

This would take care of the repeating where clause but comes with a performance penalty.

A better solution might be what EMP suggests. Normalize your tables to remove duplicate data.

SELECT  staff_key
        , SUM(CASE WHEN type = 'Sick' THEN days_applied ELSE 0 END) as sick_total_day       
        , SUM(CASE WHEN type = 'Othert' THEN days_applied ELSE 0 END) as othert_total_day       
        , SUM(CASE WHEN type = 'Otherv' THEN days_applied ELSE 0 END) as otherv_total_day       
FROM    (
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    , [Type] = 'Sick'
            FROM    Tleave_sick_leave
            UNION ALL
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    , 'Othert'
            FROM    Tleave_other_leave
            UNION ALL
            SELECT  staff_key
                    , days_applied
                    , from_date
                    , to_date
                    , 'Otherv'
            FROM    Tleave_vleave
        )
WHERE   staff_key = 131 
        AND from_date>='2011/4/1 00:00:00' 
        AND to_date<='2011/4/30 00:00:00';
GROUP BY
        staff_key

Upvotes: 2

EMP
EMP

Reputation: 62031

You probably could do it in one statement, but you would still need 3 separate WHERE clauses, because the from_date and to_date columns in each table are completely separate, despite having the same name.

It sounds like you've found an issue in your table design. You have 3 tables with very similar information. If you can combine them into one table then you can probably get away with one query, too.

Upvotes: 1

Related Questions