Need Help with Conditional SubQuery

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 2005 Forums

Transact-SQL (2005)

Need Help with Conditional SubQuery

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-08-20 : 14:35:34

There is almost definitely a better way to do this, but I am stuck on a query I am trying to create.

Basically, I am importing data from a CSV file into a temporary table in my DB. However, I later pull the data into the table it is supposed to be in, but there are a couple fields that need to conditionally be turned into an Integer value in accordance with the Foreign Key that the field has. However, these each can be Null.

Here is an example... The actual problem that I am having, is that I cannot perform the subquery part like I want to.

[CODE]
INSERT INTO [dbo].[hotel_list]
(
[hotel_id],[hotel_name],[address1],[address2],[city],[zipcode],[latitude],[longitude],
[phone],[hotrate],[video],[overview],[reviews],[map],[star_rating],[low_rate],
[currency],[hotel_description],
[state_id],[country_id],[brand_id],[price_band_id]
)
SELECT
hlt.[hotel_id],hlt.[hotel_name],hlt.[address1],hlt.[address2],hlt.[city],hlt.[zipcode],
hlt.[latitude],hlt.[longitude],hlt.[phone],hlt.[hotrate],hlt.[video],hlt.[overview],
hlt.[reviews],hlt.[map],hlt.[star_rating],hlt.[low_rate],
hlt.[currency],hlt.[hotel_description],
(CASE WHEN hlt.[state_code] IS NULL THEN NULL ELSE (SELECT hs.[state_id] FROM [dbo].[hotel_state] hs WHERE hs.[state_abbreviation] = hlt.[state_code] END)) AS [state_id],
(CASE WHEN hlt.[country_code] IS NULL THEN NULL ELSE (SELECT hc.[country_id] FROM [dbo].[hotel_country] hc WHERE hc.[country_abbreviation] = hlt.[country_code] END)) AS [country_id],
(CASE WHEN hlt.[brand_code] IS NULL THEN NULL ELSE (SELECT hb.[brand_id] FROM [dbo].[hotel_brand] hb WHERE hb.[brand_abbreviation] = hlt.[brand_code] END)) AS [brand_id],
(CASE WHEN hlt.[price_band_code] IS NULL THEN NULL ELSE (SELECT hpb.[price_band_id] FROM [dbo].[hotel_price_band] hpb WHERE hpb.[price_band_abbreviation] = hlt.[price_band_code] END)) AS [price_band_id]
FROM [dbo].[hotel_list_TEMP] hlt
[/CODE]
I am not able to get the CASE statements parsed.

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 14:48:25

[code]INSERT dbo.hotel_list
(
[hotel_id],
[hotel_name],
[address1],
[address2],
[city],
[zipcode],
[latitude],
[longitude],
[phone],
[hotrate],
[video],
[overview],
[reviews],
[map],
[star_rating],
[low_rate],
[currency],
[hotel_description],
[state_id],
[country_id],
[brand_id],
[price_band_id]
)
SELECT hlt.[hotel_id],
hlt.[hotel_name],
hlt.[address1],
hlt.[address2],
hlt.[city],
hlt.[zipcode],
hlt.[latitude],
hlt.[longitude],
hlt.[phone],
hlt.[hotrate],
hlt.[video],
hlt.[overview],
hlt.[reviews],
hlt.[map],
hlt.[star_rating],
hlt.[low_rate],
hlt.[currency],
hlt.[hotel_description],
hs.[state_id],
hc.[country_id],
hb.[brand_id],
hpb.[price_band_id]
FROM dbo.hotel_list_TEMP AS hlt
LEFT JOIN [dbo].[hotel_state] AS hs ON hs.[state_abbreviation] = hlt.[state_code]
LEFT JOIN [dbo].[hotel_country] AS hc ON hc.[country_abbreviation] = hlt.[country_code]
LEFT JOIN [dbo].[hotel_brand] AS hb ON hb.[brand_abbreviation] = hlt.[brand_code]
LEFT JOIN [dbo].[hotel_price_band] AS hpb ON hpb.[price_band_abbreviation] = hlt.[price_band_code][/code]

E 12°55'05.25"
N 56°04'39.16"

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-08-20 : 15:10:40

Oh yeah... Duh! (T-SQL is obviously not my primary language...)

However, I am curious about null values. Since those fields are primary key fields, will NULL be returned if one the fields being joined on are NULL? I do not want to run into a Foreign Key Constraint error. The fields are also Nullable, so Null or a valid foreign key value are needed.

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 15:13:02

NULL values do not get JOINed and the result is a NULL.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 15:14:15

And the fine thing with LEFT JOIN is that of you have a value that do not exists in the other table, such as a country_code as 'XYZ', the result is also NULL.

E 12°55'05.25"
N 56°04'39.16"

bishalbivan
Starting Member

1 Post

Posted - 2007-08-20 : 17:39:59

INSERT INTO [dbo].[hotel_list]
(
[hotel_id],[hotel_name],[address1],[address2],[city],[zipcode],[latitude],[longitude],
[phone],[hotrate],[video],[overview],[reviews],[map],[star_rating],[low_rate],
[currency],[hotel_description],
[state_id],[country_id],[brand_id],[price_band_id]
)
SELECT
hlt.[hotel_id],hlt.[hotel_name],hlt.[address1],hlt.[address2],hlt.[city],hlt.[zipcode],
hlt.[latitude],hlt.[longitude],hlt.[phone],hlt.[hotrate],hlt.[video],hlt.[overview],
hlt.[reviews],hlt.[map],hlt.[star_rating],hlt.[low_rate],
hlt.[currency],hlt.[hotel_description],
(CASE WHEN hlt.[state_code] IS NULL THEN NULL ELSE (SELECT top 1 hs.[state_id] FROM [dbo].[hotel_state] hs WHERE hs.[state_abbreviation] = hlt.[state_code] END)) AS [state_id],
(CASE WHEN hlt.[country_code] IS NULL THEN NULL ELSE (SELECT top 1 hc.[country_id] FROM [dbo].[hotel_country] hc WHERE hc.[country_abbreviation] = hlt.[country_code] END)) AS [country_id],
(CASE WHEN hlt.[brand_code] IS NULL THEN NULL ELSE (SELECT top 1 hb.[brand_id] FROM [dbo].[hotel_brand] hb WHERE hb.[brand_abbreviation] = hlt.[brand_code] END)) AS [brand_id],
(CASE WHEN hlt.[price_band_code] IS NULL THEN NULL ELSE (SELECT top 1 hpb.[price_band_id] FROM [dbo].[hotel_price_band] hpb WHERE hpb.[price_band_abbreviation] = hlt.[price_band_code] END)) AS [price_band_id]
FROM [dbo].[hotel_list_TEMP] hlt

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-08-21 : 07:42:41

Excellent! Thanks to everyone for your help. The LEFT JOIN is perfect!

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources