join tables

Question

join tables

sean 58

Valued Member

Points: 51
More actions
January 28, 2018 at 5:23 am

#403001

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.

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

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

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

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

sean 58 Valued Member Points: 51 More actions · Answer 2

Hello Andrew,

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

Greetings, Sean