Help needed in designing query

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

Transact-SQL (2000)

Help needed in designing query

Author

Topic

t1g312
Posting Yak Master

148 Posts

Posted - 2005-05-30 : 00:51:46

Hi all,

I have the following 3 tables.

Company Master
Code Name
ABC ...
XYZ ...

Item Master
ID Name
1 A
2 B
3 C
4 D

ItemPrice
Company Code Item ID Price
ABC 1 100
EFG 2 200

I want to write a query to retrieve all the items from item master and prices, if they exist.

When I run the following query:

select Item Master.Item ID, Item Master.Name, ItemPrice.Price FROM Item Master LEFT OUTER JOIN ItemPrice on Item Master.ID = ItemPrice.Item ID WHERE Company Code = 'ABC' OR Company Code IS NULL

I get this:

Item ID Item Name Price
1 A 100
3 C ...
4 D ...

Another example:

select Item Master.Item ID, Item Master.Name, ItemPrice.Price FROM Item Master LEFT OUTER JOIN ItemPrice on Item Master.ID = ItemPrice.Item ID WHERE Company Code = 'JKL' OR Company Code IS NULL

Item ID Item Name Price
3 C ...
4 D ...

As you can see, if the prices are entered for a company, the items get omitted for other companies.

Appreciate if someone could correct the query.

Thanks in advance,

Adi

-------------------------
/me sux @sql server

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-30 : 04:06:46

[code]

--- gives you all items with their total
SELECT [Item Master].ItemID, [Item Master].Name,
ISNULL((Select sum(Price) from ItemPrice IP where IP.[Item ID] = [Item Master].ItemID),0) AS
TotalPrice
FROM [Item Master]

[/code]

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

t1g312
Posting Yak Master

148 Posts

Posted - 2005-05-30 : 06:22:15

quote:
Originally posted by raclede

--- gives you all items with their total
SELECT [Item Master].ItemID, [Item Master].Name,
ISNULL((Select sum(Price) from ItemPrice IP where IP.[Item ID] = [Item Master].ItemID),0) AS
TotalPrice
FROM [Item Master]

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

Are you sure you read my question? :)

Adi

-------------------------
/me sux @sql server

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-05-30 : 06:42:27

You don't want to constrain your resultset, only the left join (to prices pertaining to a particular company). You can do this by adding criteria to the join:


SELECT 
	im.[Item ID], 
	im.[Name], 
	ip.[Price] 
FROM 
	dbo.[Item Master] AS im 
	LEFT OUTER JOIN dbo.ItemPrice AS ip
		ON im.[ID] = ip.[Item ID] 
			AND ip.[Company Code]= 'ABC'

Mark

t1g312
Posting Yak Master

148 Posts

Posted - 2005-05-30 : 07:00:22

Hi Mark,

I am getting the exact same result as I was earlier!

Adi

-------------------------
/me sux @sql server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources