T sql

  • Hi all,

    I need to create report and need help.Here are 2 tables with some data.

    CREATE TABLE table1

    (ID INT, Value VARCHAR(10))

    INSERT INTO Table1 (ID, Value)

    SELECT 1,'First'

    UNION ALL

    SELECT 2,'Second'

    UNION ALL

    SELECT 3,'Third'

    UNION ALL

    SELECT 4,'Fourth'

    UNION ALL

    SELECT 5,'Fifth'

    GO

    CREATE TABLE table2

    (ID INT, dueDate DATETIME)

    INSERT INTO Table2 (ID, dueDate)

    SELECT 1,'2014-01-01'

    UNION ALL

    SELECT 2,'2014-01-02'

    UNION ALL

    SELECT 3,'2014-01-03'

    UNION ALL

    SELECT 6,'2014-01-04'

    UNION ALL

    SELECT 7,'2014-01-05'

    UNION ALL

    SELECT 8,'2014-01-06'

    GO

    I need all id's from table1 and dueDate from table2 for specific dueDate.If table2 dueDate does not match my dueDate then show 'null' or any 0.if matches then show dueDate.This is how it should look is i say dueDate='2014-01-02':

    IDValuedueDate

    1FirstNULL

    2Second2014-01-02

    3ThirdNULL

    4FourthNULL

    5FifthNULL

    this is script that came up with which is not what i want

    SELECT t1.*,t2.dueDate

    FROM Table1 t1

    LEFT JOIN Table2 t2 ON t1.ID = t2.ID

    WHERE t2.dueDate='2014-01-02'

    GO

    Thank You

  • Thanks for the DDL... that' helps a lot. But could you post the result that you are looking for? Then someone can figure out how to do the query.

  • Several ways of doing this, here is one suggestion using a CTE. The important thing here is to pre-filter the second set because any filtering after joining the sets will effectively turn the left outer join / cross/outer apply into an inner join.

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.table1') IS NOT NULL DROP TABLE dbo.Table1;

    CREATE TABLE dbo.Table1

    (ID INT, Value VARCHAR(10))

    INSERT INTO dbo.Table1 (ID, Value)

    SELECT 1,'First'

    UNION ALL

    SELECT 2,'Second'

    UNION ALL

    SELECT 3,'Third'

    UNION ALL

    SELECT 4,'Fourth'

    UNION ALL

    SELECT 5,'Fifth'

    GO

    IF OBJECT_ID(N'dbo.table2') IS NOT NULL DROP TABLE dbo.Table2;

    CREATE TABLE dbo.Table2

    (ID INT, dueDate DATETIME)

    INSERT INTO dbo.Table2 (ID, dueDate)

    SELECT 1,'2014-01-01'

    UNION ALL

    SELECT 2,'2014-01-02'

    UNION ALL

    SELECT 3,'2014-01-03'

    UNION ALL

    SELECT 6,'2014-01-04'

    UNION ALL

    SELECT 7,'2014-01-05'

    UNION ALL

    SELECT 8,'2014-01-06'

    GO

    DECLARE @dueDate DATETIME = '2014-01-02';

    ;WITH BASE_DATA AS

    (

    SELECT

    T2.ID

    ,T2.dueDate

    FROM dbo.Table2 T2

    WHERE T2.dueDate = @dueDate

    )

    SELECT

    T1.ID

    ,T1.Value

    ,T2.dueDate

    FROM dbo.Table1 T1

    LEFT OUTER JOIN BASE_DATA T2

    ON T1.ID = T2.ID;

    Results

    ID Value dueDate

    ----------- ---------- -----------------------

    1 First NULL

    2 Second 2014-01-02 00:00:00.000

    3 Third NULL

    4 Fourth NULL

    5 Fifth NULL

  • Barcelona10 (2/26/2015)


    SELECT t1.*,t2.dueDate

    FROM Table1 t1

    LEFT JOIN Table2 t2 ON t1.ID = t2.ID

    WHERE t2.dueDate='2014-01-02'

    GO

    SELECT t1.*,t2.dueDate

    FROM Table1 t1

    LEFT JOIN Table2 t2 ON t1.ID = t2.ID

    AND t2.dueDate='2014-01-02'

  • serg-52 (2/27/2015)


    Barcelona10 (2/26/2015)


    SELECT t1.*,t2.dueDate

    FROM Table1 t1

    LEFT JOIN Table2 t2 ON t1.ID = t2.ID

    WHERE t2.dueDate='2014-01-02'

    GO

    SELECT t1.*,t2.dueDate

    FROM Table1 t1

    LEFT JOIN Table2 t2 ON t1.ID = t2.ID

    AND t2.dueDate='2014-01-02'

    Quick word of caution, although a simple solution, it can lead to a rather inefficient execution plan compared to the ones where the set is pre-filtered, even for this small sample, the cost ratio is 1:4

    😎

  • Already answered in dupe post

    http://www.sqlservercentral.com/Forums/Topic1664045-391-1.aspx

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Thank You ALL.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply