LEFT Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

LEFT Join

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-21 : 13:43:14

I wrote this query to get the list of warehouses and number of products (for that particular product) in each warehouse.

But it is returning only the warehouse which has that product.

I want to return all the warehouses and the products in the warehouse.

PROCEDURE [ORDER_SelectWarehouse]
@code VARCHAR(50)
AS

SELECT w.[warehouse_type_id]
,w.[name]
,ISNULL(COUNT(pw.product_id),0) AS NumberOfProducts
FROM [Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON w.warehouse_type_id = pw.warehouse_type_id
LEFT JOIN Product AS p ON pw.product_id=p.product_id
WHERE (p.product_code = @code OR p.upc_code = @code)
GROUP BY w.warehouse_type_id, w.name

Thanks.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-21 : 14:08:59

You may have to give an example of your tables structure and desired results...

This looks similar to one we just posted on a minute ago...

Sounds like you have -

Warehouses
A
B
C

Products
1
2

CountofProducts
A , 1
B, 2
A,1

Results -

Warehouse   PruductID   CountOf
A,    1,     2
A,    2,     0
B,    1,     0
B,    2,     1
C,    1,     0
C,    2,     0

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-21 : 14:09:00

[code]
SELECT
w.[warehouse_type_id]
,w.[name]
,ISNULL(COUNT(pw.product_id),0) AS NumberOfProducts
FROM
[Warehouses] AS w
LEFT JOIN
Product_Warehouse AS pw
ON w.warehouse_type_id = pw.warehouse_type_id
LEFT JOIN
Product AS p
ON pw.product_id=p.product_id
WHERE
isnull(p.product_code,'') = @code
or
isnull(p.upc_code,'') = @code
GROUP BY w.warehouse_type_id, w.name
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-21 : 14:20:46

Thanks a lot.

I did it like this

SELECT w.[warehouse_type_id]
,w.[name]
,0
FROM [dbo].[Warehouses] AS w

UNION ALL

SELECT w.[warehouse_type_id]
,w.[name]
,ISNULL(COUNT(pw.product_id),0) AS NumberOfProducts
FROM [dbo].[Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON w.warehouse_type_id = pw.warehouse_type_id
LEFT JOIN Product AS p ON pw.product_id=p.product_id
WHERE (p.product_code = @code OR p.upc_code = @code)
GROUP BY w.warehouse_type_id, w.name, p.product_id
ORDER BY w.name

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-12-21 : 14:20:54

The problem is that your where clause gets evaluated after the joins. So before the where is evaluated, your Product Codes will be NULL for the warehouses that don't have Products, then your where clause is eliminates these rows.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-12-21 : 14:23:49

quote:
Originally posted by mapidea

Thanks a lot.

I did it like this

SELECT w.[warehouse_type_id]
,w.[name]
,0
FROM [dbo].[Warehouses] AS w

UNION ALL

SELECT w.[warehouse_type_id]
,w.[name]
,ISNULL(COUNT(pw.product_id),0) AS NumberOfProducts
FROM [dbo].[Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON w.warehouse_type_id = pw.warehouse_type_id
LEFT JOIN Product AS p ON pw.product_id=p.product_id
WHERE (p.product_code = @code OR p.upc_code = @code)
GROUP BY w.warehouse_type_id, w.name, p.product_id
ORDER BY w.name

Please don't be offended: but this query looks like it is a work around without understanding the original problem!

I think this query will show warehouses twice, once with the 0 and once with the count, I don't thint you want that?

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-21 : 14:57:00

Right.

How can I correct that?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-21 : 15:15:49

Have you tried Vinnie's solution? Isn't that what you need?

quote:
Originally posted by mapidea

Right.

How can I correct that?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-12-21 : 15:18:40

[code]SELECT
w.[warehouse_type_id],
w.[name],
pw_count.NumberOfProducts
FROM
[Warehouses] AS w LEFT JOIN
( select warehouse_type_id,
ISNULL(COUNT(pw.product_id),0) AS NumberOfProducts
from Product AS p, Product_Warehouse AS pw
where pw.product_id=p.product_id and
(p.product_code = @code OR p.upc_code = @code)
GROUP BY pw.warehouse_type_id
) AS pw_count
ON w.warehouse_type_id = pw_count.warehouse_type_id[/code]

Does this work?
Does it make sense?

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-21 : 15:19:00

I did it like this

SELECT w.[warehouse_type_id]
,w.[name]
,0
FROM [Promax_office7].[dbo].[Warehouses] AS w
WHERE w.[warehouse_type_id] NOT IN
(
SELECT w.[warehouse_type_id]
FROM [Promax_office7].[dbo].[Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON w.warehouse_type_id = pw.warehouse_type_id
LEFT JOIN Product AS p ON pw.product_id=p.product_id
WHERE (p.product_code = @code OR p.upc_code = @code)
GROUP BY w.warehouse_type_id
)

UNION ALL

SELECT w.[warehouse_type_id]
,w.[name]
,ISNULL(COUNT(pw.product_id),0) AS NumberOfProducts
FROM [Promax_office7].[dbo].[Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON w.warehouse_type_id = pw.warehouse_type_id
LEFT JOIN Product AS p ON pw.product_id=p.product_id
WHERE (p.product_code = @code OR p.upc_code = @code)
GROUP BY w.warehouse_type_id, w.name, p.product_id
ORDER BY w.name

I know there is a better way to do this.

I would appreciate if you can point that out.

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-21 : 15:27:00

Perfect. Makes Sense. Thanks a lot.

mapidea
Posting Yak Master

124 Posts

Posted - 2009-12-21 : 15:28:54

What do you think of this.

I think this works but will have performance issues.

SELECT w.[warehouse_type_id]
,w.[name]
,0
FROM [Promax_office7].[dbo].[Warehouses] AS w
WHERE w.[warehouse_type_id] NOT IN
(
SELECT w.[warehouse_type_id]
FROM [Promax_office7].[dbo].[Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON w.warehouse_type_id = pw.warehouse_type_id
LEFT JOIN Product AS p ON pw.product_id=p.product_id
WHERE (p.product_code = @code OR p.upc_code = @code)
GROUP BY w.warehouse_type_id
)

UNION ALL

SELECT w.[warehouse_type_id]
,w.[name]
,ISNULL(COUNT(pw.product_id),0) AS NumberOfProducts
FROM [Promax_office7].[dbo].[Warehouses] AS w
LEFT JOIN Product_Warehouse AS pw ON w.warehouse_type_id = pw.warehouse_type_id
LEFT JOIN Product AS p ON pw.product_id=p.product_id
WHERE (p.product_code = @code OR p.upc_code = @code)
GROUP BY w.warehouse_type_id, w.name, p.product_id
ORDER BY w.name

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-12-21 : 16:08:00

quote:
Originally posted by mapidea

What do you think of this.

I think this works but will have performance issues.

I guess it would work but it has a lot of copy & paste i.e. repeated code, which is an ideal thing to avoid. And yes, I would think it would be slower. But on the other hand it looks like it would return the correct result.

If you were to use that in your Application or Stored Proc, I would certainly add a comment like: 'Union all warehouses without Products WITH all warehouses with Products:'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources