CODEWITHSUNDEEP
mysqlsqldatabase
kitokid
kitokid

Reputation: 3117

ifnull() function in where clause MYSQL

I have two tables.

**Employee**
EmpId NOT NULL (PK)
Name NOT NULL
OrgID NULL (FK)

**Organisation**
OrgID NOT NULL (PK)
OrgName NOT NULL

I want to get Name,OrgName with one select statement. but condition is OrgID can be null in Employee table. So many records without OrgID and records with OrgID too.

How can get all data by passing EmpId with one sql statement?

Thanks.

Upvotes: 1

Views: 562

Answers (4)

Sgoettschkes
Sgoettschkes

Reputation: 13214

This should work fine:

SELECT 
  e.Name
  , o.OrgName 
FROM Employee AS e 
LEFT JOIN Organisation AS o ON e.OrgID=o.OrgID 
WHERE e.EmpId = ?;

Upvotes: 0

zveljkovic
zveljkovic

Reputation: 444

SELECT * FROM Employee 
LEFT JOIN Organisation ON 
    Employee.OrgID == Organisation.OrgID 
WHERE 
    EmpId = ?;

This link will give you more details

EDIT: Forgot to include WHERE clause

Upvotes: 0

ain
ain

Reputation: 22769

Use LEFT JOIN ie

SELECT
  e.Name,
  O.OrgName
FROM Employee e
LEFT JOIN Organisation O ON(O.OrgID = e.OrgID)
WHERE EmpId = x

Upvotes: 1

Bohemian
Bohemian

Reputation: 425448

Use a LEFT JOIN:

select e.name, o.OrgName 
from Employee e
left join Organisation o on o.OrgID = e.OrgID
where e.EmpId = ?;

OrgName will be null in the result rows if the FK is null.

Upvotes: 3

Related Questions