显示表中其他表不包含的行

Question

基本上，我想显示某个表中的所有数据，而另一个表不包含具有特定条件的数据。

我想向所有没有填写可用性的人展示。这就是我所做的，看看谁在SQL中填写了他们的可用性：

SELECT availability_id, availability_date, availability_start, availability_end, info_id, info_firstname, info_surname, job_name
FROM Availability
FULL JOIN Info
ON Availability.availability_info_id = Info.info_id
LEFT JOIN Job
ON Job.job_id = Info.info_job_id
WHERE Availability.availability_date = '2019-9-20'; //today

所以这显示了今天可用的人。

现在我想向那些没有填写可用性的人展示。因此，我想向表">信息"中的每个人显示不在表">可用性"中的每个人，其中日期= 2019-09-20。

我该怎么做？

Answer 1

我认为以下内容应该这样做。 它列出了"信息"中特定日期的可用性中没有记录的所有内容。

SELECT  I.* 
FROM    Info I
WHERE NOT EXISTS (
SELECT 1
FROM    Availability A
WHERE   A.availability_info_id = I.info_id
AND A.availability_date = '2019-9-20' -- today
)

Answer 2

--use derived table  and case statment:
--you can give your date in case statement and give proper aliases in order to avoid errors during execution
--this query will give you all records from info table and you can also get info for availability and not available also

SELECT *
FROM (
SELECT Availability.availability_id, Availability.availability_date, Availability.availability_start, Availability.availability_end, Info.info_id, Info.info_firstname, Info.info_surname, Job.job_name
,case when  Availability.availability_date = '2019-09-20' then 'AVAILABLE'
ELSE 'NOT AVAILABLE'
END STATUS
FROM        Info
left join   Availability
ON          Availability.availability_info_id = Info.info_id
LEFT JOIN   Job
ON          Job.job_id = Info.info_job_id
WHERE Availability.availability_date = '2019-09-20'; --//today
) T
WHERE STATUS='NOT AVAILABLE' --THIS WILL GIVE YOU INFO WHICH HAS NO '2019-09-20' IN availability_date COLUMN