MS SQL Selecting all rows in one table and only rows matching in a second

[Steven J Jowett]

I have 2 tables, Products and Receipts. In need to select all rows in the Products table and link it to the Receipts tables where the ReceiptId equals a given value.

Products Table

ProductId BIGINT

Receipts Table

ReceiptId BIGINT
ProductId BIGINT
Quantity DECIMAL(15,4)

If I execute a SELECT query on the products table I get 126 rows returned If I execute a SELECT query on the receipts table WHERE ReceiptId = 330804 , I get 2 rows. I created a new view as follows

SELECT TOP (100) PERCENT dbo.Products.ProductId, dbo.Receipts.ReceiptId, dbo.Receipts.Quantity
FROM dbo.Products LEFT OUTER JOIN
dbo.Receipts ON dbo.Products.ProductId = dbo.Receipts.ProductId

Then if I perform a SELECT query on the new view as follows :-

SELECT * FROM vuProductReceipts WHERE ReceiptId = 330804 OR ReceiptId IS NULL

I would expect to get 126 rows returned, but I am only getting 63. Any help would be appreciated. Thanks

Steve Jowett ------------------------- Real programmers don't comment their code. If it was hard to write, it should be hard to read.

[J4amieC]

Steven J Jowett wrote:

If I execute a SELECT query on the receipts table WHERE ReceiptId = 330804 , I get 2 rows.

Steven J Jowett wrote:

SELECT * FROM vuProductReceipts WHERE ReceiptId = 330804 OR ReceiptId IS NULL

Steven J Jowett wrote:

I would expect to get 126 rows returned, but I am only getting 63.

Based on the above three asserions, im guessing there are 61 products without any receipts. What's your question?

[Niladri_Biswas]

Hi, But I have used your code only and is getting the correct result. declare @tblProduct table(Productid int ) insert into @tblProduct select 1 union all select 2 union all select 3 union all select 4 union all select 5 declare @tblReceipts table(Receiptid int,Productid int,Quantity decimal ) insert into @tblReceipts select 1,1,20 union all select 1,1,30 union all select 2,1,40 union all select 2,1,12 union all select 3,1,45 union all select 2,2,45 union all select 1,1,20 union all select 1,1,30 union all select 2,1,40 union all select 2,1,12 union all select 2,1,45 union all select 2,2,45

select ProductId,ReceiptId,Quantity from
(SELECT TOP (100) PERCENT p.ProductId, r.ReceiptId, r.Quantity
FROM @tblProduct p LEFT JOIN
@tblReceipts r
ON p.ProductId = r.ProductId ) X(ProductId,ReceiptId,Quantity)
where X.ReceiptId = 2 OR X.ReceiptId is NULL

The output is as desired:

ProductId ReceiptId Quantity
1 2 40
1 2 12
1 2 40
1 2 12
1 2 45
2 2 45
2 2 45
3 NULL NULL
4 NULL NULL
5 NULL NULL

So what is ur doubt? Can u give us some datapoints so that we can give a better insight! :)

Niladri Biswas

[David Skelly]

You can't really figure out what's going on here without knowing more about the data that is actually in the tables. There are two rows in the Receipts table with ReceiptId 330804. Do these have the same ProductId or different ProductIds? My guess is that there are two products for receipt id 330804 and 61 products with no receipt id. Total, 63 rows in your result set.

[Steven J Jowett]

I have a table called products with a unique id column called ProductId. The products table contains 124 rows. I have another table called receipts containing

ReceiptId
ProductId
Quantity

The ProductId would contain a ProductId from the Products table. So what I want is a list of 124 products and the quantities received next to each product with a particular ReceiptId Basically

ProductId Quantity
1.........10
2.........NULL
3.........NULL
4.........5
5.........NULL

NULL being where there is no receipt

Steve Jowett ------------------------- Real programmers don't comment their code. If it was hard to write, it should be hard to read.

[David Skelly]

This page covers everything you need: http://www.firstsql.com/tutor3.htm[^] The sections you are interested in are Outer Join, Group By and Set Functions (specifically, SUM).

[Steven J Jowett]

Dave Thanks for the help. My solution is as follows :-

SELECT NULL as TicketId, *, NULL as Quantity
FROM vuDepotProducts
WHERE NOT EXISTS(
SELECT * FROM vuReceipts WHERE DepotId = vuDepotProducts.DepotId AND ProductId = vuDepotProducts.ProductId AND TicketId = 330804
)
UNION
SELECT * FROM vuReceipts WHERE vuReceipts.TicketId = 330804

Steve Jowett ------------------------- Real programmers don't comment their code. If it was hard to write, it should be hard to read.