Help with full Join query

[doobybug]

Hi I am trying to make a query to combine 2 other queries:

I have 1 query JunOrd (Shows all the orders in June)
OrderNo
OProdCode
OProduct
OSize
OSizeUnit
OPack
OCase
OQtyNet
OQtyFOC
TOQty

The other query is JunInv (Shows all the invoices in June)
OrderNo
SProdCode
SProduct
SSize
SSizeUnit
SPack
SCase
SQtyNet
SQtyFOC
TSQty

I want to create a query that combines the 2 queries together showing me:
OrderNo, OProductCode, TOQty, OrderNo, SProductCode, TSQty

I want to show even the blanks where there is no product code for example in the order or there is a product code in the order but has not been invoiced yet.

I want the query to be according to OrderNo

Can someone help me?

 

[PHV]

SQL code:

SELECT O.OrderNo, OProductCode, TOQty, I.OrderNo, SProductCode, TSQty
FROM JunOrd O LEFT JOIN JunInv I ON O.OrderNo = I.OrderNo
UNION SELECT O.OrderNo, OProductCode, TOQty, I.OrderNo, SProductCode, TSQty
FROM JunOrd O RIGHT JOIN JunInv I ON O.OrderNo = I.OrderNo

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[doobybug]

This gives me much more records that I have! It is repeating for sure the Orders! Any ideas why?

 

[PHV]

So, why not simply this ?

SELECT O.OrderNo, OProductCode, TOQty, I.OrderNo, SProductCode, TSQty
FROM JunOrd O INNER JOIN JunInv I ON O.OrderNo = I.OrderNo

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[doobybug]

still the right hand side is giving more records than the left! In need some 200 records and it is giving me 9000! records on the right side are being duplicated

 

[kjv1611]

If the OrderNo is the same in both queries, then it'd be more like this:

SELECT O.OrderNo, OProdCode, TOQty, SProdCode, TSQty
FROM JunOrd O INNER JOIN JunInv I ON O.OrderNo = I.OrderNo

But if you are wanting to make sure you don't eliminate any records, it should be like this, I think:

SELECT O.OrderNo, OProdCode, TOQty, SProdCode, TSQty
FROM JunOrd O OUTER JOIN JunInv I ON O.OrderNo = I.OrderNo

--

"If to err is human, then I must be some kind of human!" -Me