unfoudev unfoudev - 6 months ago 22
SQL Question

SQL: combining two sql queries

I have two tables:

T_CHAMBRE_CHB (CHB_NUM);

T_PLANNING_PLN (PLN_JOUR ,CHB_NUM, PLN_LIBRE);


I have these values in it:

T_CHAMBRE_CHB:

1
2
3
4


T_PLANNIG_CHB:

1 2000-01-12 1 False
2 2000-01-12 2 False
3 2000-01-13 1 False
4 2000-01-13 2 False
5 2000-01-13 4 True


I would like to get the rooms occpation the 13 janvier 2000 for information if a room is not in the planning (here 3) that's mean it's free, so the result should be:

CHB_NUM PLN_LIBRE
----------- ---------
1 False
2 False
3 True
4 True


I have this query:

SELECT DISTINCT TCC.CHB_NUM, TPP.PLN_LIBRE
FROM T_CHAMBRE_CHB AS TCC,
T_PLANNING_PLN AS TPP
WHERE TCC.CHB_NUM=TPP.CHB_NUM AND TPP.PLN_JOUR = '2000-01-13'
UNION
SELECT DISTINCT TCC.CHB_NUM, TPP.PLN_LIBRE
FROM T_CHAMBRE_CHB AS TCC,
T_PLANNING_PLN AS TPP
WHERE TCC.CHB_NUM NOT IN (SELECT DISTINCT (TPP2.CHB_NUM) FROM T_PLANNING_PLN AS TPP2);


I get this result:

1 1 False
2 2 False
3 3 False
4 3 True
5 4 True


I don't know why I get the third line ( 3---->False )

I think I should use an OUTER UNION but SQL Server doesn't like the syntax.

Answer

Below query might give the required result :-

declare @date as date='2000-01-13'

select distinct TCC.CHB_NUM,COALESCE(TPP.PLN_JOUR,@date) PLN_JOUR,COALESCE(TPP.PLN_LIBRE,'True') PLN_LIBRE    
from T_CHAMBRE_CHB TCC
LEFT OUTER JOIN
T_PLANNING_PLN TPP ON TCC.CHB_NUM=TPP.CHB_NUM
WHERE TPP.PLN_JOUR=@date OR TPP.PLN_JOUR IS NULL

Output :-

 CHB_NUM    PLN_JOUR    PLN_LIBRE
    1      2000-01-13   False
    2      2000-01-13   False
    3      2000-01-13   True
    4      2000-01-13   True