June 22, 2005 at 4:44 pm
Hi everybody.
I have a table which contains record of listing. Listing record has fields RegionID, CityID, SuburbID, only RegionID being compulsory - others optional. I also have three more table corresponding to Regions, Cities and Suburbs.
Now when I select a listing record, I have to check if the cityid is not null and if it not null then get the City's name else an empty string. The same goes for the Suburbs too.
Thanks in advance.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply