select case in a record set - 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)

select case in a record set

Author

Topic

abc
Starting Member

48 Posts

Posted - 2002-04-25 : 01:08:16

Hello

Thanks for reading and now try to help me. Thanks for all your advices
I got three tables as follow:

Contry
Country_ID int (PK)
Country_Name char

city
City_ID int (PK)
Country_ID int (FK)
City_Name char

Market
Market_ID (PK)
Country_ID (FK) Null
City_ID (FK) Null
market_Name Null

I want to write a SP to return Market_ID and Market_Name in that.
If Market_Name is Null and City_ID is Null, I'll got Market_ID and Country_Name
If Market_Name is Null and City_ID is not Null I'll got Market_ID and City_Name
If Market_Name is not Null I'll got Market_ID, Country_Name, City_Name, Market_Name

How can I do that ???

Thanks for your attention

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-04-25 : 04:57:41

You can use a UNION statement to return one recordset with all three conditions.

SELECT Market_Id,
Country_Name = (SELECT Country_name from Contry WHERE Country_ID = Market.Country_ID),
City_Name = '',
Market_Name = ''
FROM Market
WHERE market_name IS NULL AND city_id IS NULL
UNION
SELECT market_id,
Country_Name = '',
City_Name = (SELECT City_name from City WHERE City_ID = Market.City_ID),
Market_Name = ''
FROM Market
WHERE market_name IS NULL AND city_id IS NOT NULL
UNION
SELECT market_id,
Country_Name = (SELECT Country_name from Contry WHERE Country_ID = Market.Country_ID),
City_Name = (SELECT City_name from City WHERE City_ID = Market.City_ID),
market_name
FROM Market
WHERE market_name is not null

HTH

abc
Starting Member

48 Posts

Posted - 2002-04-25 : 23:07:41

Yellowbug,
Thanks for advice, It's ok now, but I got one more question. If I want to order the result of these selects statement (after used union)
by CountryName, CityName, MarketName, so how to do that.

Thanks for your attention

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-04-26 : 04:32:41

Place the ORDER BY clause at the end of the entire statement

...
FROM Market
WHERE market_name is not null
ORDER BY Country_Name, City_Name, Market_Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources