All from data from two tables, where data may not exists in 2nd ta

[Guest]

Hi all,

I am getting lost in the world of joins...inners..outers..etc

Here is my reuest/problem/plea...

I have one table that records

Year,Month(1..12),Product,QtySold

My Second Table records

Date,Product,QtyRejected

In the second table I record on any given day the qty rejected by product.

What I am trying to do Is show is

Year, Month, Product, Qty Sold, Qty Rejected

It is possible to have a rejection but have a qty sold, also we might not have
any rejections in a month.

My Query should look like

Year Month Qty Sold Qty rejected
2004 1 100000 0
2004 2 100000 5
2004 3 0 2
2004 4 0 0
etc until
2004 12 0 0

But it only produces

2004 2 100000 5

this I think is becuase there is no record in 2nd table relating to any
rejections
for that product in a month.

Hope it make sense.........and big thanks for reading

Marcus

 

[Noozer]

Hi all,

I am getting lost in the world of joins...inners..outers..etc

What does your current Query look like?

 

[Guest]

I actually a very similiar issue with 3 tables of mine. I'm interested in
what type of answers you get from here.

 

[Guest]

Hi,

SELECT Table2.product, Table2.qtysold, Table1.qty
FROM Table2 INNER JOIN Table1 ON Table2.product = Table1.product;

I have simplified the query, the results I get are

product qtysold qty
a 10 1
b 10 5

Table1 has

date product qty
01/01/2001 a 1
01/01/2001 b 5

Table2 has

product qtysold
a 10
b 10
c 10
d 0

What I would like to see

product qtysold qty
a 10 1
b 10 5
c 10 0
d 0 0

Many thanks

 

[Guest]

Sorted!!!

SELECT LineNumberTbl.LineNumber, Sum(wrcust.Qty) AS CustomerUnits,
Year([DOC]) AS Expr1, Month([DOC]) AS Expr2
FROM LineNumberTbl LEFT JOIN wrcust ON LineNumberTbl.LineNumber =
wrcust.LineNumber
GROUP BY LineNumberTbl.LineNumber, Year([DOC]), Month([DOC])
HAVING (((LineNumberTbl.LineNumber)=[enter line number]) AND
((Year([DOC]))=[enter year]));

I required another table with all the linenumbers and then used LEFT JOIN

Many thanks All