CODEWITHSUNDEEP
sqlsql-server
HarisJayadev
HarisJayadev

Reputation: 92

select table using complicated query

enter image description here

I have 2 tables with above fields.

I have created below query

  SELECT DISTINCT H.HolidayId,H.HolidayDate,H.Description,CH.ClientId,
        case when CH.HolidayId is not null then 1 else 0 end as  IsHoliday
        FROM Holiday H LEFT JOIN ClientHolidays CH ON h.HolidayId = CH.HolidayId

enter image description here

But I need to list all holidays with IsHoliday field if client has added as holiday then IsHoliday field should come as 1 else 0

And when querying with ClientId. It should list all holidays depend upon clientId

I need result as below

enter image description here

While passing client id i need to get all holidays with IsHoliday field should come as 0 or 1

Upvotes: 0

Views: 79

Answers (5)

HarisJayadev
HarisJayadev

Reputation: 92

I have used your queries after 2 days only i found bug in that. then i found a solution for that.

Below is the solution:

  SELECT h.HolidayId, HolidayDate,Description,CASE WHEN ClientID IS NULL THEN 0 WHEN ClientID != @ClientId THEN 0   ELSE 1 END AS IsHoliday
                        FROM HOLIDAY h LEFT OUTER JOIN CLIENTHOLIDAYS ch ON ch.HolidayId = h.HolidayId
                        WHERE ClientId = @ClientId OR ClientID is null or ClientID is not null

Thank you

Upvotes: 0

Oliver
Oliver

Reputation: 167

From your comments I get the sense you don't want all holidays. You just want the holidays for each client. If that is the case you should use an inner join . Also in this scenario the isHoliday field is moot because you only get the results for holidays that clients actually take so is holiday will always be 1.

SELECT H.HolidayId,H.HolidayDate,H.Description,CH.ClientId,
       case when CH.HolidayId is not null then 1 else 0 end as  IsHoliday
FROM Holiday H inner JOIN 
ClientHolidays CH 
ON h.HolidayId = CH.HolidayId

Upvotes: 0

Azar
Azar

Reputation: 1867

Try This

SELECT DISTINCT H.HolidayId
    ,H.HolidayDate
    ,H.Description
    ,CH.ClientId
    ,CASE 
        WHEN CH.clientid IS NOT NULL
            THEN 1
        ELSE 0
        END AS IsHoliday
FROM Holiday H
LEFT JOIN ClientHolidays CH 
    ON h.HolidayId = CH.HolidayId

Upvotes: 0

Jesuraja
Jesuraja

Reputation: 3844

Try this:

SELECT  DISTINCT H.HolidayId,H.HolidayDate,H.Description,
        CASE WHEN CH.HolidayId IS NOT NULL THEN 1 ELSE 0 END AS IsHoliday
FROM    Holiday H LEFT JOIN 
        ClientHolidays CH ON h.HolidayId = CH.HolidayId
WHERE   CH.ClientId = 8 OR CH.ClientId IS NULL

Upvotes: 0

roopaliv
roopaliv

Reputation: 447

Try below query, it will work as per the requirement :)

SELECT h.HolidayId, HolidayDate,
CASE WHEN ClientID IS NULL THEN 0 ELSE 1 END AS IsHoliday
FROM HOLIDAY h
LEFT OUTER JOIN CLIENTHOLIDAYS ch ON ch.HolidayId = h.HolidayId
WHERE ClientId = 1 OR ClientID is null

Upvotes: 2

Related Questions