CODEWITHSUNDEEP
sqlsql-serverjoin
razor
razor

Reputation: 173

How to get columns from second table only if data exists otherwise should return null?

I am struggling with SQL Joins concept and am facing problem when I am trying to get student attendance record. There are 3 tables that have to be used.

attendance a - has all student attendance

st_holiday b, - student specific holiday public list hol_list c - all holiday list including weekend

There are 2 questions: 1. I need data that gives me in_time, out_time and holiday_type to me. Student can also attend class on his holiday date(can be public holiday or weekend).

I tried below query which gives me only holiday attended dates.

select a.student_id, a.in_time, a.out_time, b.holiday_type
from attendance a, st_holiday b, hol_list c
where a.st_id = b.st_id
and b.holiday = c.id
and a.date = c.hol_date

I have also tried using left outer join st_holiday on a.st_id = b.st_id and left outer join holiday_type on a.date = c.hol_date But it gives me all data and for each data holiday_type shows as Holiday/weekend, etc.

2. How to parameterize by date. I need to provide the data as sql script with date range parameterized. How to do the same in Sql Server. I tried using 

DECLARE from_date='2015-04-01 00:00:00.000'
DECLARE to_date='2015-04-01 00:00:00.000'

SELECT * FROM TABLE 
WHERE .... 
AND [PI].Date BETWEEN ISNULL(@FromDate, [PI].Date) AND ISNULL(@ToDate, [PI].Date)

I used below link for reference. sql server optional parameters: syntax for between clause Any help will be appreciated.

Update : Table Structure. attendance a - primary Key ID, st_id shared with st_holiday table st_holiday b, - pk is ID, holiday shared with hol_list table hol_list c - hol_date can be used with date of attendance table.

Expected output:

St_id       IN_TIME            OUT_TIME                 Hol_type
1234  2015-08-07 08:00:00.000  2015-08-07 17:00:00.000  null
1234  2015-08-08 08:00:00.000  2015-08-08 08:00:00.000  Weekend

Table structure : st_id in attendance and st_holiday table are not unique. The value will be repeated. For eg. eah st_id in st_holiday will have 20 holidays for very year i.e. st_id is repeated 20 times. In attendance table st_id will depend on no. of times student comes in and goes out of classroom.

attendance 
    -------------
    st_id         in_time              out_time      time_in_class
    3370    2009-10-8 11:54:0.0 2009-10-8 18:1:0.0  6.11666666666667
    4209            0                     0               0
    4225            0                     0               0
    3779    2009-10-8 14:27:0.0 2009-10-9 0:5:0.0   9.63333333333333


    hol_list
    -------------
    id           hol_date          type        desc             holiday_type

    229 2007-04-06 00:00:00.000     1       Good Friday             Holiday
    231 2007-05-01 00:00:00.000     1       International Labor Day Holiday
    233 2007-07-04 00:00:00.000     1       Independence Day        Holiday
    234 2007-07-07 00:00:00.000     1       Weekend                 Weekend


    st_holiday
    -------------
    st_id   holiday     holiday_type
     9201   39965         Holiday
     9201   39961         Holiday
     9201   39951         Holiday
     9201   39942         Holiday
     9201   39935         Weekend
     9201   39927         Holiday

Upvotes: 3

Views: 612

Answers (1)

rgstamayo
rgstamayo

Reputation: 163

Based on your sample query and explanation. For your first question, kindly give this a try:

select a.student_id, a.in_time, a.out_time, b.holiday_type
from attendance a 
left join st_holiday b on a.st_id = b.st_id
inner join hol_list c on a.date = c.hol_date

Upvotes: 2

Related Questions