CODEWITHSUNDEEP
phpsql
Akki Verma
Akki Verma

Reputation: 49

query sql join and where clause

First table employee

id   empName     empEmail           empSal   active
====================================================
13   sadf        [email protected]       233       1
12   checkout    [email protected]    3         1
11   safg        [email protected]      123       1
10   sdf         sadf@sdf           4         1
9    sdaf        [email protected]       3         1
8    asdf        [email protected]       8         1
7    asdf        [email protected]       8         1
6    asdf        [email protected]       8         1

Second Table emp_status

id  emp_id  EmpSalary  active
=============================
5   4       156        1
4   2       156        1
3   2       555        1
2   1       200        0
1   1       1500       1



SELECT o.*
FROM employees as o
LEFT JOIN emp_status as emp ON emp.emp_id = o.id
WHERE (emp.active IS NULL OR (emp.active != 0))
GROUP BY o.id
ORDER BY o.id DESC

If emp_status has two rows one having status 1 and other having status 0 what we want it should not count to 1 if I have zero it should help out the execution. how could it be possible?

Upvotes: 0

Views: 59

Answers (1)

Reena Mori
Reena Mori

Reputation: 645

Don't pass join table condition on where clause and I suggest you active column datatype =ENUM And set value 0,1,2,3,4,5,6

SELECT o.* FROM employees as o
LEFT JOIN emp_status as emp ON emp.emp_id = o.id and emp.active IN (1,2,3,5,6) GROUP BY o.id ORDER BY o.id DESC

Upvotes: 2

Related Questions