left join question/problem - 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 2000 Forums

SQL Server Development (2000)

left join question/problem

Author

Topic

newcents3
Starting Member

3 Posts

Posted - 2006-08-01 : 15:28:29

I have a products table:
products_ID, list_price
1, $200
2, $300
etc..

and a customer pricing table:
customer_pricing_ID, products_ID, customer_ID, customer_price
1, 2, 5, $100
1, 2, 4, $150

I'd like to select all the products in the products table and left join the customer table. All the products in the products table should be returned. But where the customer_ID=5 that customer pricing is shown for that row.

So doing a left join for customer_ID 5 would produce:
products_ID, list_price, customer_ID, customer_price
1, $200, NULL, NULL, NULL
2, $300, 2, 5, $100

When I set the where in the statement to where customer_ID=5, only that customers products are returned.

Any help greatly appreciated

newcents3
Starting Member

3 Posts

Posted - 2006-08-01 : 15:47:06

Okay I figured it out:
Select part_description, (select top 1 customer_price
from products_customers spc where spc.products_ID=sp.products_ID and spc.customers_ID=15) as customer_price from products sp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 16:24:31

[code]-- prepare test data
declare @products table (products_id int, list_price money)

insert @products
select 1, $200 union all
select 2, $300

declare @customerpricing table (customer_pricing_ID int, products_ID int, customer_ID int, customer_price int)

insert @customerpricing
select 1, 2, 5, $100 union all
select 1, 2, 4, $150

-- do the work
SELECT p.products_id,
p.list_price,
cp.products_id,
cp.customer_id,
cp.customer_price
FROM @Products p
LEFT JOIN @CustomerPricing cp on cp.products_id = p.products_id
WHERE (cp.customer_id = 5 OR cp.customer_id IS NULL)[/code]

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-01 : 18:07:06

Just put the customer_id = 5 condition in the join clause.

-- prepare test data
declare	@products table (
products_id int,
list_price money)

insert	@products
select	1, $200 union all
select	2, $300

declare	@customerpricing table (
customer_pricing_ID int,
products_ID int,
customer_ID int,
customer_price int)

insert	@customerpricing
select	1, 2, 5, $100 union all
select	1, 2, 4, $150

-- do the work
SELECT
	p.products_id,
	p.list_price,
	cp.products_id,
	cp.customer_id,
	cp.customer_price
FROM
	@Products p
	LEFT JOIN
	@CustomerPricing cp
	on
	cp.products_id = p.products_id and
	cp.customer_id = 5

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources