how to show records with JOIN?

Hi ,

I've got two tables.. the first table carried a ProductID, and amongst other things a TradePrice

The other tbl carries a ProductID, a IndivPrice and a CustomerID

The second tbl lists prices for products for indiv Customers.

My Query needs to bring back ALL the products from the first tbl...

It also needs to show the TradePrice for that product.

I need to join my query to the second tbl...

And finally, if the second tbl has a price for that product AND the customerID is the same as one I pass into the query.. show that price also..

So here's my first query:

SELECT dbo.Products.ProductID, ProductName, ProductTradePrice, IndivPrice, dbo.Trade_PriceLists.CustomerID AS PLCustomerID FROM dbo.Products LEFT OUTER JOIN dbo.Trade_PriceLists ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID WHERE (ProductType = 'Trade' OR ProductType = 'Both') AND (Replace(Lower(ProductBrand),' ','') = 'brandname') AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '') ORDER BY TradeOrder

I thought that would work, but what happens is that, if that particular customer has no indiv prices set.. then it only shows the ones that have no records at all in that second tbl..

So unless there is a record for a particular product in that second tbl and it doesn't have a CustomerID assigned to (which would never happen as that tbl is only every for indiv customer prices) then it doesn't show.

Examples:

First Tbl

ProductID Name TradePrice

1 Jumper ￡1.00

2 Jeans ￡3.00

3 Shoes ￡5.00

4 Hat ￡2.00

Second Tbl

ProductID CustomerID IndivPrice

1 teste ￡0.50

2 othercustomer ￡2.50

3 teste ￡4.50

What I want in the results is:

ProductID ProductName TradePrice IndivPrice CustomerID (PLCustomerID)

1 Jumper ￡1.00 ￡0.50 teste

2 Jeans ￡3.00

3 Shoes ￡5.00 ￡4.50 teste

4 Hat ￡2.00

See? - The 2nd product should not get an indiv price as although it's in that second tbl, the customerID assigned to it is different. The 4th product should not get an indiv price as it's not in that second tbl at all.

however, with my query above I'd only get Products 1and 3... and if I did a query on a customer with no indiv prices I'd only get product 4 as it's not in the indiv at all...

HELP!!!!!

Give a look to the SELECT article in books online. Maybe something like:

declare @.firstTbl table
( ProductId integer,
Name varchar(10),
Price money
)
insert into @.firstTbl
select 1, 'Jumper', $1.0 union all
select 2, 'Jeans', $3.0 union all
select 3, 'Shoes', $5.0 union all
select 4, 'Hat', $2.0
--select * from @.firstTbl

declare @.secondTbl table
( ProductId integer,
CustomerId varchar(15),
IndivPrice money
)
insert into @.secondTbl
select 1, 'teste', $0.5 union all
select 2, 'othercustomer', $2.5 union all
select 3, 'teste', $4.5
--select * from @.secondTbl

select a.productId,
a.name as [ProductName],
a.Price as [Trade Price],
coalesce(convert(varchar(21),b.IndivPrice), '') as IndivPrice,
coalesce(convert(varchar(21),b.CustomerId), '') as [CustomerId (PlCustomerId)]
from @.firstTbl a
left join @.secondTbl b
on a.productId = b.productId

/*
productId ProductName Trade Price IndivPrice CustomerId (PlCustomerId)
-- -- -- -- -
1 Jumper 1.0000 0.50 teste
2 Jeans 3.0000 2.50 othercustomer
3 Shoes 5.0000 4.50 teste
4 Hat 2.0000
*/

|||

Hi Kent,

Thanks for the reply..

Your select statement looks virtually the same as mine!

However your's is missing the WHERE clause - and this is what is causing the problem.

My WHERE clause needs to bring back records that either have no CustomerID, or the specific CustomeID I ask for in the WHERE statement.

However the WHERE clause I have inserted (see my first post) simply does not do it. If I put in a Customer ID into my where clause that doesn't have an indivprice records.. then it simply returns nothing...

whereas it should return all of the records... but obivously these would not have an indivprice for them

|||

Is this better:

declare @.firstTbl table
( ProductId integer,
Name varchar(10),
Price money
)
insert into @.firstTbl
select 1, 'Jumper', $1.0 union all
select 2, 'Jeans', $3.0 union all
select 3, 'Shoes', $5.0 union all
select 4, 'Hat', $2.0
--select * from @.firstTbl

declare @.secondTbl table
( ProductId integer,
CustomerId varchar(15),
IndivPrice money
)
insert into @.secondTbl
select 1, 'teste', $0.5 union all
select 2, 'othercustomer', $2.5 union all
select 3, 'teste', $4.5
--select * from @.secondTbl

select a.productId,
a.name as [ProductName],
a.Price as [Trade Price],
coalesce(convert(varchar(21),b.IndivPrice), '') as IndivPrice,
coalesce(convert(varchar(21),b.CustomerId), '') as [CustomerId (PlCustomerId)]
from @.firstTbl a
left join @.secondTbl b
on a.productId = b.productId
and customerId = 'teste'

/*
productId ProductName Trade Price IndivPrice CustomerId (PlCustomerId)
-- -- -- -- -
1 Jumper 1.0000 0.50 teste
2 Jeans 3.0000
3 Shoes 5.0000 4.50 teste
4 Hat 2.0000
*/

I have a question: Are you wanting results 2 and 4 removed?

|||

thanks!! - that's perfect!!

Can you let me know what is different about your query.. I see that indivprice has a convert on it and there is a Coalesce statement..

What are these for?

|||

Hang on and I'll give it a go.

The COALESCE / VARCHAR business is to replace an output that would otherwise say NULL with blank space.

After I reworked your original query and used your table names what I got was:

SELECT dbo.Products.ProductID,
ProductName,
ProductTradePrice,
coalesce(convert(varchar(21), IndivPrice), '') as IndivPrice,
coalesce(dbo.Trade_PriceLists.CustomerID, '') AS PLCustomerID
FROM dbo.Products
LEFT OUTER JOIN dbo.Trade_PriceLists
ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID
WHERE (ProductType = 'Trade'
OR ProductType = 'Both')
AND (Replace(Lower(ProductBrand),' ','') = 'brandname')
AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '')

/*
ProductID ProductName ProductTradePrice IndivPrice PLCustomerID
-- --
1 Jumper 1.0000 0.50 teste
3 Shoes 5.0000 4.50 teste
4 Hat 2.0000
*/

|||

- excellent - thank you very much...

|||

one final question.

If I use your query - then the Indiv price is now converted into a varchar and no longer is a money field.

I need this to be a money field for my code (it's used in a shopping basket...

How would I do this..

(I've tried converting it in my Server Side ASP using CLng, but it won't do it..

|||Then don't use the VARCHAR / COALESCE on this particular field -- provided that your application can handle the situation when the field is a NULL output. I just realized that when I reworked your query I dropped a row; is that wanted?|||

just noticed that myself... no I need all results back..

why did it drop a record?

|||

To avoid dropping that record one of the lineds needs to be moved up and become part of the JOIN instead of part of the WHERE clause. Also, I removed the conversion of the PRICE field:

SELECT dbo.Products.ProductID,
ProductName,
ProductTradePrice,
IndivPrice,
coalesce(dbo.Trade_PriceLists.CustomerID, '') AS PLCustomerID
FROM dbo.Products
LEFT OUTER JOIN dbo.Trade_PriceLists
ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID
AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '')
WHERE (ProductType = 'Trade'
OR ProductType = 'Both')
AND (Replace(Lower(ProductBrand),' ','') = 'brandname')

/*
ProductID ProductName ProductTradePrice IndivPrice PLCustomerID
-- --
1 Jumper 1.0000 .5000 teste
2 Jeans 3.0000 NULL
3 Shoes 5.0000 4.5000 teste
4 Hat 2.0000 NULL
*/