Return all records and return JOIN records if they exists

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)

Return all records and return JOIN records if they exists

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-23 : 10:37:43

Ray writes "Hi from Australia guys,

We have two tables: tblProducts and tblMemberPrice (See schema below), users visiting the site may be members and will have a memberType (Integer Eg. 1) assigned to them.

We want to list all the products and prices from tblProducts and if a matching record for the product is found in tblMemberPrice it needs to use that price instead.

Each product may have mutiple entries in tblMemberPrice with the memberType number different for different member levels.

Eg.
priceID    1
productID  1
memberType 1
amount     100

priceID    2
productID  1
memberType 2
amount     90

So the query would need to only return results where the users memberType matched (Eg. WHERE memberType=1)

As soon as you specify 'memberType=1' in the WHERE clause, it excludes any entries that don't have a matching entry in tblMemberPrice.

Currently we're looking at making a separate query for each product which is a lot more of an overhead on the server.

Is there a way with one SQL query to list all the products, and return any applicable member prices if there is a matching entry in tblMemberPrice?

Looking forward to a possible solution.

Schema of tblProducts and tblMemberPrice

---------------------
tblProducts
---------------------
productID     (Int - Identity)
productName   (nvarChar)
salePrice     (money)
-------------------

---------------------
tblMemberPrice
---------------------
priceID       (Int - Identity)
productID     (int)
memberType    (Int)
amount        (money)
---------------------

nr
SQLTeam MVY

12543 Posts

Posted - 2002-05-23 : 10:53:38

select *
from tblProducts p
left outer join tblMemberPrice mp
on p.productID = mp.productID
and mp.memberType = 1

You have to put the memberType check in the join. If you put it in the whare clause it will filter afte rrthe jon and you will lose the product records.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

dsdeming

479 Posts

Posted - 2002-05-23 : 13:35:56

I think you need something like:

COALESCE( tblMemberPrice.amount, tblProducts.amount )

This will return the first non-null value. If you're done an outer join between the tables and there is no matching row in tblMemberPrice, tblMemberPrice.amount will be null and you'll get tblProducts.amount; otherwise tblMemberPrice.amount will not be null and you'll get that value. COALESCE is just an ANSI-standard ISNULL except that it can take more than 2 parameters.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources