Query with Joins | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query with Joins

Hello I have two tables PROJECT ( with columns PROJECT_ID , TL , FL , PM )and PROJDATE(with columns PROJECT_ID , delivery_date, Delivered_date). The Project_id between the 2 tables donot have any keys. SELECTnt.Project_ID,nt.TL,nt.FL,nt.PM FROM PROJECT AS nt
INNER JOIN( SELECT ProjectID,MAX(CASE WHEN DateType = ‘Start date’ THEN DeliveryDate
ELSE NULL
END) AS StartDate,
MAX(CASE WHEN DateType = ‘End date’ THEN DeliveryDate
ELSE NULL
END) AS EndDate,
MAX(CASE WHEN DateType = ‘End date’ THEN DeliveredDate
ELSE NULL
END) AS ActualEndDate FROM PROJDATE
GROUP BYProjectID
) AS x ON x.ProjectID = nt.Project_ID
ORDER BYnt.Project_ID I am trying to get , the output as Project_id,TL , FL , PM , start_date , enddate , delivered date. With the above query , I am able to get only the data from the 1st table i.e Project_ID,TL,FL . How can I get the whole data. from both tables. Thanks for any help
You have a derived table with the alias X. Within the derived table, you have added an alias for each of the expressions, like StartDate. In your main query, just use X.* or X.StartDate, like you would for columns from a regular (non-derived) table.
Sorry Adriaan , I didnot get what you are telling me. Like where I should exactly use X. in the main query.
Can you explain it . Thanks
Hi Adriaan, I have got it. Thanks a lot for the help.
]]>