SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


join tables


join tables

Author
Message
sean 58
sean 58
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 20

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.
Andrew P
Andrew P
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3592 Visits: 2037
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
sean 58
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 20
Hello Andrew,

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

Greetings, Sean
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search