SQL Server Performance

Query with Joins

Discussion in 'T-SQL Performance Tuning for Developers' started by vaddi, Apr 13, 2007.

  1. vaddi New Member

    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
  2. Adriaan New Member

    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.
  3. vaddi New Member

    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
  4. vaddi New Member

    Hi Adriaan,

    I have got it. Thanks a lot for the help.

Share This Page