Help joining tables properly.

[Ciar?n]

I have about 7 tables I need to join, but am having a lot of
difficulty with the joins, that I need some help on.

I'll provide the details of four tables to illustrate the scenario.

I have one table called Product, which contains a complete list of
products.
I have another table called Date, which contains a complete list of
dates.
I have a table called sales, which contains Product, date and Qty.
I have a table called Purchases which contains product, date and qty.

I want to link my Purchases and sales tables to my Product and date
tables.

On each row I want product from the Product table, Date from the date
table, Sales Qty from the sales table and Purchases Qty from the
Purchases table.

I've been messing around with full outer, inner joins etc, but can't
get it right.

All help greatfull.

Regards,
Ciarán

[Dan Guzman]

To answer your question, we need to know your table schema, especially the
primary and foreign keys. From your narrative, it seems an INNER JOIN
(matching rows only) will provide the desired results.

Please post your DDL (CREATE TABLE statements)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ciar?n" <ch********@hotmail.com> wrote in message
news:7f**************************@posting.google.c om...

I have about 7 tables I need to join, but am having a lot of
difficulty with the joins, that I need some help on.

I'll provide the details of four tables to illustrate the scenario.

I have one table called Product, which contains a complete list of
products.
I have another table called Date, which contains a complete list of
dates.
I have a table called sales, which contains Product, date and Qty.
I have a table called Purchases which contains product, date and qty.

I want to link my Purchases and sales tables to my Product and date
tables.

On each row I want product from the Product table, Date from the date
table, Sales Qty from the sales table and Purchases Qty from the
Purchases table.

I've been messing around with full outer, inner joins etc, but can't
get it right.

All help greatfull.

Regards,
Ciarán

[Ciar?n]

Dan,

I don't have any create table statements...
Basically what has happened is that I received a number of text file
exports from another system.

All the files have a date and product field, so using union statements
I created master lists of all products and all dates and am now hoping
to link all the files based on these.

I have tried using inner joins, but they fail as I could have sales of
a product on one day, but no purchases and as a result inner joins
fail to capture the sales. Hope that makes sense. I need a statement
that will capture all sales and purchases data.

All help greatly appreciated.

Ciarán
"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in message news:<jo******************@newssvr12.news.prodigy. com>...

To answer your question, we need to know your table schema, especially the
primary and foreign keys. From your narrative, it seems an INNER JOIN
(matching rows only) will provide the desired results.

Please post your DDL (CREATE TABLE statements)

--
Hope this helps.

Dan Guzman
SQL Server MVP

[Dan Guzman]

> I don't have any create table statements...

One method to generate the DDL is using Query Analyzer. Right-click on the
object in the object browser and select 'script object...as CREATE'.

I have tried using inner joins, but they fail as I could have sales of
a product on one day, but no purchases and as a result inner joins
fail to capture the sales. Hope that makes sense. I need a statement
that will capture all sales and purchases data.
You can use a LEFT join to select all products regardless of whether or not
you have purchases or sales. A CROSS JOIN will return all possible
combinations of Product and Dates. Example below.
CREATE TABLE Product
(
ProductCode int NOT NULL
CONSTRAINT PK_Product PRIMARY KEY,
ProductName varchar(30) NOT NULL
)

CREATE TABLE Calendar
(
CalendarDate datetime NOT NULL
CONSTRAINT PK_Dates PRIMARY KEY
)

CREATE TABLE Sales
(
ProductCode int NOT NULL,
SaleDate datetime NOT NULL,
Qty int NOT NULL,
CONSTRAINT PK_Sales PRIMARY KEY
(ProductCode, SaleDate),
CONSTRAINT FK_Sales_Producct FOREIGN KEY
(ProductCode) REFERENCES Product(ProductCode)
)

CREATE TABLE Purchases
(
ProductCode int NOT NULL,
PurchaseDate datetime NOT NULL,
Qty int NOT NULL,
CONSTRAINT PK_Purchases PRIMARY KEY
(ProductCode, PurchaseDate),
CONSTRAINT FK_Purchases_Producct FOREIGN KEY
(ProductCode) REFERENCES Product(ProductCode)
)

SET NOCOUNT ON

INSERT INTO Product VALUES(1,'Product1')
INSERT INTO Product VALUES(2,'Product2')
INSERT INTO Product VALUES(3,'Product3')
INSERT INTO Product VALUES(4,'Product4')
INSERT INTO Product VALUES(5,'Product5')

INSERT INTO Calendar VALUES('20041110')
INSERT INTO Calendar VALUES('20041111')
INSERT INTO Calendar VALUES('20041112')
INSERT INTO Calendar VALUES('20041113')
INSERT INTO Calendar VALUES('20041114')

INSERT INTO Purchases VALUES(1, '20041111', 3)
INSERT INTO Purchases VALUES(2, '20041113', 4)
INSERT INTO Purchases VALUES(3, '20041114', 5)

INSERT INTO Sales VALUES(2, '20041111', 2)
INSERT INTO Sales VALUES(3, '20041113', 1)
INSERT INTO Sales VALUES(4, '20041113', 4)

SELECT
Calendar.CalendarDate,
Product.ProductCode,
Purchases.Qty AS PurchasesQuantity,
Sales.Qty AS SalesQuantity
FROM Product
CROSS JOIN Calendar
LEFT JOIN Purchases ON
Purchases.ProductCode = Product.ProductCode AND
Purchases.PurchaseDate = Calendar.CalendarDate
LEFT JOIN Sales ON
Sales.ProductCode = Product.ProductCode AND
Sales.SaleDate = Calendar.CalendarDate
ORDER BY
Calendar.CalendarDate,
Product.ProductCode

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ciar?n" <ch********@hotmail.com> wrote in message
news:7f**************************@posting.google.c om... Dan,

I don't have any create table statements...
Basically what has happened is that I received a number of text file
exports from another system.

All the files have a date and product field, so using union statements
I created master lists of all products and all dates and am now hoping
to link all the files based on these.

I have tried using inner joins, but they fail as I could have sales of
a product on one day, but no purchases and as a result inner joins
fail to capture the sales. Hope that makes sense. I need a statement
that will capture all sales and purchases data.

All help greatly appreciated.

Ciarán
"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in message
news:<jo******************@newssvr12.news.prodigy. com>...

To answer your question, we need to know your table schema, especially
the
primary and foreign keys. From your narrative, it seems an INNER JOIN
(matching rows only) will provide the desired results.

Please post your DDL (CREATE TABLE statements)

--
Hope this helps.

Dan Guzman
SQL Server MVP

[Erland Sommarskog]

Ciar?n (ch********@hotmail.com) writes:

I don't have any create table statements...
If you don't have any CREATE TABLE statements, you don't have any tables...
Basically what has happened is that I received a number of text file
exports from another system.

All the files have a date and product field, so using union statements
I created master lists of all products and all dates and am now hoping
to link all the files based on these.

I have tried using inner joins, but they fail as I could have sales of
a product on one day, but no purchases and as a result inner joins
fail to capture the sales. Hope that makes sense. I need a statement
that will capture all sales and purchases data.

....and if you don't have any tables, you don't have any need of any
statements. So I suspect that you have tables anyway.

You can script your tables from Query Analyzer, by using the Object
Browser (press F8 to make it appear, if you don't see it), finding the
table, and then right-click to pick the script alternative for CREATE
TABLE.

Be sure that your constraints are included.

And we don't ask this only to be pedantic. We ask this, because we don't
like to guess, but want to give you an accurate answer.

It also often helps to include sample data in form of INSERT statements,
and the desired output given the sample.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Thug Passion]

> If you don't have any CREATE TABLE statements, you don't have any tables...

Sure you can. And you can generate new create statements from them,
but once the tables have been created, you don't really need the old
commands anymore.

It also often helps to include sample data in form of INSERT statements,
and the desired output given the sample.

The desired results is really the thing. What the pk/fk relationships
might have nothing to do with what they guy is trying to find out.