CODEWITHSUNDEEP
sqlmysql-5.5
Prakash
Prakash

Reputation: 600

SQL Query to fetch data from 4 different tables

I have four tables and I need to fetch data from one table with where condition and the output contains ID's from three different tables using those ID's need to get the names of them.

Company:
CompanyID - PK
CompanyName
CompanyDescription

Users:
UserID - PK
FirstName
LastName
Email

TaskDetails:
TaskID - PK
CompanyID - FK of Company.CompanyID

TaskStatus:
TaskStatusID - PK
TaskID - FK of TaskDetails.TaskID
Status
Details
CreatedBy - FK of Users.UserID
UpdatedBy - FK of Users.UserID
CreatedAt
UpdatedAt

Need a query to return something like below:

CompanyName,Email,Status,Details,CreatedAt,UpdatedAt with where condition on TaskStatus table TaskStatus.UpdatedBy!=1 and TaskStatus.UpdatedAt>'2013-08-01' and TaskStatus.status='COMPLETED'

Upvotes: 2

Views: 1662

Answers (1)

Prakash
Prakash

Reputation: 600

Select CM.CompanyName as CompanyName, U.Email as User, TS.Status as Status, TS.Comments as Comments, TS.CreateDate as CreateDate, TS.UpdateDate as UpdateDate
FROM 
 TaskStatus as TS
 LEFT JOIN TaskDetails as TD ON TD.TaskID = TS.TaskID
 LEFT JOIN Company as CM on CM.CloudID = TD.CloudID
 LEFT JOIN Users as U on U.UserID = TS.UpdatedBy
WHERE
 TS.CreateDate>'2013-08-01' and TS.UpdatedBy!=1 and TS.Status='COMPLETED'
GROUP by CM.CompanyName, U.Email;

Upvotes: 1

Related Questions