Query Similar Date from 5 Diffrent Tables

  • Dear Experts,

    I have 3 different tables that I would like to query data from and put it in the following format

    ConsignmentValue Duedate CardCode Farm1 Farm2 Farm3

    325 01/05/2011 C200006 500 200 300

    340 01/05/2011 C200007 100

    342 01/05/2011 C200032 50 100

    The tables all have similar data but the column ConsignmentValue can be found in all 3 tables and it is used as the connector. Such that from Consignment Value 325 I should dislplay the Credit Data from the 3 Farms in the the layout above.

    The problem I face is that where the consignment value has been repeated in the tables more than once where the duedate is diffrent I get replications which mess up my query, due to the large number of rows in the tables the query fails to execute.

    Kindly Find the DDLs below.

    CREATE TABLE #mytable1

    (

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    DateValue DATETIME,

    MyValue DECIMAL(9,4),

    Credit INT,

    CARDCODE VARCHAR (20),

    ConsigmentValue VARCHAR (20),

    Farm1 Varchar (20),

    )

    CREATE TABLE #mytable2

    (

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    DateValue DATETIME,

    MyValue DECIMAL(9,4),

    Credit INT,

    CARDCODE VARCHAR (20) INT,

    ConsigmentValue VARCHAR (20),

    Farm1 Varchar (20),

    )

    CREATE TABLE #mytable2

    (

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    DateValue DATETIME,

    MyValue DECIMAL(9,4),

    Credit INT,

    CARDCODE VARCHAR (20) INT,

    ConsigmentValue VARCHAR (20),

    Farm1 Varchar (20),

    )

    // This should populate all 3 tables as the data held is similar.

    SELECT '5766','May 1 2011 12:00AM','11206.800000','C200006','325','Farm1' UNION ALL

    SELECT '5767','May 1 2011 12:00AM','11206.800000','C200007','325','Farm2' UNION ALL

    SELECT '5768','May 1 2011 12:00AM','2460.760000','C200032','340','Farm3' UNION ALL

    SELECT '5769','May 1 2011 12:00AM','2460.760000','C200118','342','Farm1' UNION ALL

    SELECT '5770','May 1 2011 12:00AM','23696.240000','C200038','339','Farm2' UNION ALL

    SELECT '5771','May 1 2011 12:00AM','35316.540000','C200033','338','Farm3' UNION ALL

    SELECT '5772','May 1 2011 12:00AM','3873.430000','C200045','51','Farm3' UNION ALL

    SELECT '5773','May 1 2011 12:00AM','6835.460000','C200117','51','Farm2'

    Please help get solution for this.

  • Can you not join all three tables on the ConsignmentValue field and just display the date?

    select a.date1, b.date2, c.date3

    from a

    inner join b

    on a.ConsignmentValue = b.ConsignmentValue

    inner join c

    on a.ConsignmentValue = c.ConsignmentValue

  • I agree with Steve. I would just join on consignmentvalue. Is that field always a numeric? If so, I would change it to a different datatype than varchar.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi Steve,

    For rows that don't have the consignment number using the INNER JOIN will leave them out. How can I get around that ?

  • Hi Steve,

    I still get replication when I do this. Is there a way I could control this ?

  • martin.edward (4/5/2011)


    Hi Steve,

    I still get replication when I do this. Is there a way I could control this ?

    When you say replication, do you mean repeated rows?

    You are getting this as a result set:

    ConsignmentValue Duedate CardCode Farm1 Farm2 Farm3

    325 01/05/2011 C200006 500 200 300

    340 01/10/2011 C200007 100

    342 01/25/2011 C200032 50 100

    340 01/05/2011 C200007 100

    342 01/05/2011 C200032 50 100

    When you are looking for this:

    ConsignmentValue Duedate CardCode Farm1 Farm2 Farm3

    325 01/05/2011 C200006 500 200 300

    340 01/05/2011 C200007 100

    342 01/05/2011 C200032 50 100

    If I guessed correctly, you have a few choices. Are you looking for the greatest date? Using two rows from the above example:

    340 01/10/2011 C200007 100

    340 01/05/2011 C200007 100

    If you want the row with the date 01/01/2011, then a multiple subqueries should work.

    SELECT T1.ConsigmentValue, T1.DateValue, T1.CardCode, T1.Farm1

    FROM Table1 as T1

    INNER JOIN (SELECT MaxValue.ConsigmentValue, MAX(MaxValue.DateValue) as MaxDateValue

    FROM Table1 as MaxValue

    GROUP BY MaxValue.ConsigmentValue) as MaxValue ON T1.ConsigmentValue = MaxValue.ConsigmentValue

    AND T1.DateValue = MaxValue.DateValue

    The cautions with this approach would be if the dates also repeat. You will get multiple rows. In that case, if you can use the most recently extered values, using MAX on the identity fields may also work. Replace the DateVaue with the ID field.

    You also have NULLS to deal with, which may require you to perform a left join.

    You can then join all three subqueries to get your values.

    SELECT A.DateValue, B.DateValue, C.DateValue

    FROM

    (SELECT T1.ConsigmentValue, T1.DateValue, T1.CardCode, T1.Farm1

    FROM Table1 as T1

    INNER JOIN (SELECT MaxValue.ConsigmentValue, MAX(MaxValue.DateValue) as MaxDateValue

    FROM Table1 as MaxValue

    GROUP BY MaxValue.ConsigmentValue) as MaxValue ON T1.ConsigmentValue = MaxValue.ConsigmentValue

    AND T1.DateValue = MaxValue.DateValue) as A

    INNER JOIN

    (SELECT T2.ConsigmentValue, T2.DateValue, T2.CardCode, T2.Farm1

    FROM Table2 as T2

    INNER JOIN (SELECT MaxValue.ConsigmentValue, MAX(MaxValue.DateValue) as MaxDateValue

    FROM Table2 as MaxValue

    GROUP BY MaxValue.ConsigmentValue) as MaxValue ON T2.ConsigmentValue = MaxValue.ConsigmentValue

    AND T2.DateValue = MaxValue.DateValue) as B ON A.ConsigmentValue = B.ConsigmentValue

    INNER JOIN

    (SELECT T3.ConsigmentValue, T3.DateValue, T3.CardCode, T3.Farm1

    FROM Table3 as T3

    INNER JOIN (SELECT MaxValue.ConsigmentValue, MAX(MaxValue.DateValue) as MaxDateValue

    FROM Table3 as MaxValue

    GROUP BY MaxValue.ConsigmentValue) as MaxValue ON T3.ConsigmentValue = MaxValue.ConsigmentValue

    AND T3.DateValue = MaxValue.DateValue) as C ON A.ConsigmentValue = C.ConsigmentValue

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael has some good questions. Replication, BTW, is not repetition or repeated rows. It also means something else in SQL Server.

    I missed the sentence about duplicate values, and my apologies. However I don't see anything in there about missing the Consignment values. You have to have some value that will join tables together. If you don't, then how do you handle that? Is it guaranteed to be in one table?

  • Well, the consignment no. is the unique link on all the tables.

  • martin.edward (4/4/2011)


    Dear Experts,

    I have 3 different tables that I would like to query data from and put it in the following format

    ConsignmentValue Duedate CardCode Farm1 Farm2 Farm3

    325 01/05/2011 C200006 500 200 300

    340 01/05/2011 C200007 100

    342 01/05/2011 C200032 50 100

    The tables all have similar data but the column ConsignmentValue can be found in all 3 tables and it is used as the connector. Such that from Consignment Value 325 I should dislplay the Credit Data from the 3 Farms in the the layout above.

    The problem I face is that where the consignment value has been repeated in the tables more than once where the duedate is diffrent I get replications which mess up my query, due to the large number of rows in the tables the query fails to execute.

    Kindly Find the DDLs below.

    CREATE TABLE #mytable1

    (

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    DateValue DATETIME,

    MyValue DECIMAL(9,4),

    Credit INT,

    CARDCODE VARCHAR (20),

    ConsigmentValue VARCHAR (20),

    Farm1 Varchar (20),

    )

    CREATE TABLE #mytable2

    (

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    DateValue DATETIME,

    MyValue DECIMAL(9,4),

    Credit INT,

    CARDCODE VARCHAR (20) INT,

    ConsigmentValue VARCHAR (20),

    Farm1 Varchar (20),

    )

    CREATE TABLE #mytable2

    (

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    DateValue DATETIME,

    MyValue DECIMAL(9,4),

    Credit INT,

    CARDCODE VARCHAR (20) INT,

    ConsigmentValue VARCHAR (20),

    Farm1 Varchar (20),

    )

    // This should populate all 3 tables as the data held is similar.

    SELECT '5766','May 1 2011 12:00AM','11206.800000','C200006','325','Farm1' UNION ALL

    SELECT '5767','May 1 2011 12:00AM','11206.800000','C200007','325','Farm2' UNION ALL

    SELECT '5768','May 1 2011 12:00AM','2460.760000','C200032','340','Farm3' UNION ALL

    SELECT '5769','May 1 2011 12:00AM','2460.760000','C200118','342','Farm1' UNION ALL

    SELECT '5770','May 1 2011 12:00AM','23696.240000','C200038','339','Farm2' UNION ALL

    SELECT '5771','May 1 2011 12:00AM','35316.540000','C200033','338','Farm3' UNION ALL

    SELECT '5772','May 1 2011 12:00AM','3873.430000','C200045','51','Farm3' UNION ALL

    SELECT '5773','May 1 2011 12:00AM','6835.460000','C200117','51','Farm2'

    Please help get solution for this.

    Martin,

    If you'll take the time to actually make the "DLL's" work and actually populate the tables with data, I'll try to lend a hand here. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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