CODEWITHSUNDEEP
sqlsql-server-2008
HEEN
HEEN

Reputation: 4727

Get All employees from the query in SQL

I have a query which gives me the data for the Comp_mkey = 7 and 110. The query is as below

SELECT DISTINCT m.comp_mkey,
            c.company_name,
            m.start_date,
            m.End_date,
            m.Process_date,
            m.Months_days,
            m.Process_year,
            m.Process_Month FROM emp_mst e,
            company_mst c,
            P_Monthly_Must_Para_Hdr m WHERE c.mkey = e.comp_mkey
            AND m.comp_mkey = e.comp_mkey
            AND (m.process_date IS NOT NULL
            AND (convert(varchar,(getDate()),103)) = convert(varchar,m.process_date + 1, 103))
            AND m.Process_year = 2016
            AND Process_month = 2

The query's output is as below:-

Table output

Now what I want is,

All the employee names whose comp_mkey is in 7, 110

Kindly let me know how to get that

I am using SQL-server 2008

Upvotes: 1

Views: 76

Answers (1)

Matt
Matt

Reputation: 15061

For just the name

SELECT DISTINCT e.Emp_name
FROM emp_mst e
WHERE e.comp_mkey IN ('7', '110')

Like you said use IN

SELECT DISTINCT m.comp_mkey, c.company_name, m.start_date, m.End_date, m.Process_date, m.Months_days, m.Process_year, m.Process_Month 
FROM emp_mst e, company_mst c, P_Monthly_Must_Para_Hdr m 
WHERE c.mkey = e.comp_mkey
AND m.comp_mkey = e.comp_mkey
AND (m.process_date IS NOT NULL
AND (convert(varchar,(getDate()),103)) = convert(varchar,m.process_date + 1, 103))
AND m.Process_year = 2016
AND Process_month = 2
AND m.comp_mkey IN ('7', '110')

Also you should use explicit joins like so.

SELECT DISTINCT m.comp_mkey, c.company_name, m.start_date, m.End_date, m.Process_date, m.Months_days, m.Process_year, m.Process_Month 
FROM emp_mst e
INNER JOIN company_mst c ON c.mkey = e.comp_mkey
INNER JOIN P_Monthly_Must_Para_Hdr m ON m.comp_mkey = e.comp_mkey
WHERE (m.process_date IS NOT NULL
AND (convert(varchar,(getDate()),103)) = convert(varchar,m.process_date + 1, 103))
AND m.Process_year = 2016
AND Process_month = 2
AND m.comp_mkey IN ('7', '110')

Upvotes: 2

Related Questions