June 22, 2005 at 7:41 pm
If you're selecting from a single table then you do:
select cityname = case
when cityID is null and RegionID is null then ''
else cityname
end
from listingtable
you can tweak this to suit your needs!
**ASCII stupid question, get a stupid ANSI !!!**
June 22, 2005 at 7:41 pm
If you're selecting from a single table then you do:
select cityname = case
when cityID is null and RegionID is null then ''
else cityname
end
from listingtable
you can tweak this to suit your needs!
**ASCII stupid question, get a stupid ANSI !!!**
June 22, 2005 at 7:43 pm
sorry about the double post - something went funky just as I was posting the reply...
**ASCII stupid question, get a stupid ANSI !!!**
June 22, 2005 at 7:53 pm
SET NOCOUNT ON
DECLARE @Listing TABLE
(
RegionID INT NOT NULL,
CityID INT NULL,
SuburbID INT NULL
)
INSERT INTO @Listing VALUES (1, 1, 1)
INSERT INTO @Listing VALUES (2, 2, NULL)
INSERT INTO @Listing VALUES (3, 1, 1)
INSERT INTO @Listing VALUES (4, NULL, NULL)
INSERT INTO @Listing VALUES (5, 3, 2)
INSERT INTO @Listing VALUES (6, NULL, 3)
INSERT INTO @Listing VALUES (7, NULL, NULL)
DECLARE @Region TABLE
(
RegionID INT NOT NULL,
Region VARCHAR(100)
)
INSERT INTO @Region VALUES (1, 'Region 1')
INSERT INTO @Region VALUES (2, 'Region 2')
INSERT INTO @Region VALUES (3, 'Region 3')
INSERT INTO @Region VALUES (4, 'Region 4')
INSERT INTO @Region VALUES (5, 'Region 5')
INSERT INTO @Region VALUES (6, 'Region 6')
INSERT INTO @Region VALUES (7, 'Region 7')
DECLARE @City TABLE
(
CityID INT NOT NULL,
City VARCHAR(100)
)
INSERT INTO @City VALUES (1, 'City 1')
INSERT INTO @City VALUES (2, 'City 2')
INSERT INTO @City VALUES (3, 'City 3')
INSERT INTO @City VALUES (4, 'City 4')
INSERT INTO @City VALUES (5, 'City 5')
INSERT INTO @City VALUES (6, 'City 6')
INSERT INTO @City VALUES (7, 'City 7')
DECLARE @Suburb TABLE
(
SuburbID INT NOT NULL,
Suburb VARCHAR(100)
)
INSERT INTO @Suburb VALUES (1, 'Suburb 1')
INSERT INTO @Suburb VALUES (2, 'Suburb 2')
INSERT INTO @Suburb VALUES (3, 'Suburb 3')
INSERT INTO @Suburb VALUES (4, 'Suburb 4')
INSERT INTO @Suburb VALUES (5, 'Suburb 5')
INSERT INTO @Suburb VALUES (6, 'Suburb 6')
INSERT INTO @Suburb VALUES (7, 'Suburb 7')
/* Your Answer */
SELECT A.RegionID, COALESCE(Region, ''), COALESCE(City, ''), COALESCE(Suburb, '')
FROM
@Listing A
LEFT OUTER JOIN @Region B
ON A.RegionID = B.RegionID
LEFT OUTER JOIN @City C
ON A.CityID = C.CityID
LEFT OUTER JOIN @Suburb D
ON A.SuburbID = D.SuburbID
Regards,
gova
June 22, 2005 at 7:56 pm
Sushila - You can delete your post. Use Edit then you will have delete option
Regards,
gova
June 22, 2005 at 8:09 pm
Govinn - Thanks a bunch. Works perfectly. Would never have figured out myself. That's why I love these forums.
To Sushila:
Thanks for helping. It wasn't in a single table - which would have made things much easier.
Viewing 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply