CODEWITHSUNDEEP
sqlsql-servert-sqlsubquery
Thorin Oakenshield
Thorin Oakenshield

Reputation: 14682

How to do filter in SQL

I'm having a table with Manager details with the following fields,

 Name,   ID

and a child table Appoinments it will contain the following fields,

Manager ID , Date, Appoinment Details.

Now i need to get the maneger details from the Maneger table , those who are having no appoinment on today.

Im new to SQl . Please help me to write the Query

Upvotes: 0

Views: 104

Answers (3)

Edwin Stoteler
Edwin Stoteler

Reputation: 1228

Other answers are correct, this is a join version:

SELECT M.Name, M.ID
FROM manager M LEFT JOIN appointments A ON M.id = A.manager_id
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, A.date)) != DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
GROUP BY M.Name, M.ID;

Upvotes: 0

Devolus
Devolus

Reputation: 22084

select * from manager m
where not exists
(
    select 1
    from appointments a
    where a.manager = m.manager
    and a.date = today (whatever today in your SQL is)
)

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Note I have assumed that you are using SQL Server.

select m.*
  from manager m
 where not exists (select *
                     from appointments a
                    where m.id = a.manager_id
                      and a.date >= datediff(d,0,getdate())
                      and a.date <  datediff(d,-1,getdate()));

Note:
datediff(d,0,getdate()) = Today
datediff(d,-1,getdate()) = Tomorrow

Upvotes: 3

Related Questions