join tables

  • Goodday,
    First I have to tell you that I’m relatively new to SQL Server (2008) and that my native language is Dutch.
    I have created a table named Product with the following columns:
    Product_code numeric(7, 0) NOT NULL Primary key
    Product nvarchar(30) NOT NULL
    StartDate datetime NOT NULL
    EndDate datetime NULL
    DatePrice datetime NOT NULL
    ListPrice money NOT NULL
    For every product, I’ve created a PDF file with a description and possible extras along with their listprice. So I’ve also created a table named Document with the following columns:
    DocumentID uniqueidentifier NOT NULL Primary key
    [Document] varbinary(MAX) NOT NULL
    Doc_code numeric(6, 0) NOT NULL
    Date_document datetime NOT NULL
    During the ‘lifetime’ (the time between start- and enddate, which is at least 3 or 4 years) of a Product the listprice for one (or more ) extras  can change, which means that i have created the same PDF file for this product with another date. So, most products will have multiple PDF files, so that is the reason for the Date_document column.
    Because I want to create a webpage for displaying the products along with their pdf file(s), I want to know: how do I join these two tables.

  • If I understand correctly, for each Product, you have one or more Document records, but only the latest document should be considered active. I'm assuming you're trying to join these two tables to get a list of all Product records and the currently active Document for each Product.

    Here's a couple of examples of how to do that, one using a JOIN and one using an APPLY statement.

    CREATE TABLE Product (
        Product_code numeric(7, 0) NOT NULL IDENTITY(1,1) Primary key,
        Product nvarchar(30) NOT NULL,
        StartDate datetime NOT NULL,
        EndDate datetime NULL,
        DatePrice datetime NOT NULL,
        ListPrice money NOT NULL
    );

    INSERT INTO Product(Product, StartDate, EndDate, DatePrice, ListPrice)
    VALUES ('Transparent Window Pane', CAST('20170101' AS DATETIME), CAST('20190101' AS DATETIME), CAST('20171203' AS DATETIME), 520)
        , ('Translucent Window Pane', CAST('20180101' AS DATETIME), CAST('20180129' AS DATETIME), CAST('20180129' AS DATETIME), 1500)
        , ('Tinted Window Pane', '20170102', CAST('20170901' AS DATETIME), CAST('20170102' AS DATETIME), 700)
        
    CREATE TABLE Document (
        DocumentID uniqueidentifier NOT NULL DEFAULT(NEWID()) PRIMARY KEY,
        [Document] varbinary(MAX) NOT NULL,
        Doc_code numeric(6, 0) NOT NULL,
        Date_document datetime NOT NULL,
        Product_code numeric(7,0)
    );

    ALTER TABLE Document ADD CONSTRAINT FK_Document_Product FOREIGN KEY (Product_code) REFERENCES Product(Product_code);
    CREATE NONCLUSTERED INDEX IX_NC_Document_DateDocument_ProductCode ON Document(Date_document, Product_code ) INCLUDE (DocumentID);

    INSERT INTO Document(
        Document
        , Doc_code
        , Date_document
        , Product_code
    )
    VALUES (CAST('Transparent windows are amazing!' AS VARBINARY(MAX)), 1, CAST('20170101' AS DATETIME), 1)
        , (CAST('Item removed due to complaints regarding bird-strikes.' AS VARBINARY(MAX)), 1, CAST('20170802' AS DATETIME), 1)
        , (CAST('Translucent windows are reliable.' AS VARBINARY(MAX)), 1, CAST('20170910' AS DATETIME), 2)
        , (CAST('Tinted windows improve privacy.' AS VARBINARY(MAX)), 1, CAST('20180101' AS DATETIME), 3)
        , (CAST('Tinted windows are cost-effective.' AS VARBINARY(MAX)), 1, CAST('20180121' AS DATETIME), 3)
        , (CAST('Tinted windows reduce harmful UV radiation.' AS VARBINARY(MAX)), 1, CAST('20180131' AS DATETIME), 3)

    SELECT Product.*
    FROM Product

    SELECT document.*
    FROM Document


    -- Get each Product, and the Documents for it, excluding any Documents where the product has one or more valid documents later than the current Document's date.
    -- LEFT JOIN to include Product records that do not have a related document.
    SELECT Product.Product_code
        , document.DocumentID
    FROM Product
        LEFT JOIN Document
            ON document.Product_code = Product.Product_code
                AND document.Date_document <= GETDATE()
    WHERE NOT EXISTS (
        SELECT 1
        FROM Document AS DocumentLater
        WHERE DocumentLater.Product_code = Product.Product_code
            AND DocumentLater.Date_document <= GETDATE()
            AND DocumentLater.Date_document > document.Date_document
    )

    -- Get all Products, and get only the most recent Document (ignoring any with a future Date_document).
    -- OUTER APPLY to include products that do not have a document.
    -- Seems to be more efficient than the LEFT JOIN, and returns only one record for each product where two Document records have the same Date_document.
    SELECT Product.Product_code
        , LATEST_document.DocumentID
    FROM Product
        OUTER APPLY (
            SELECT TOP 1 document.*
            FROM Document
            WHERE document.Product_code = Product.Product_code
                AND document.Date_document <= GETDATE()
            ORDER BY document.Date_document DESC
        ) AS LATEST_DOCUMENT

  • Hello Andrew,

    Thanks for your reply. I'm going to try it out.

    Greetings, Sean

Viewing 3 posts - 1 through 2 (of 2 total)

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