Need to properly implement conditional sub-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 2005 Forums

Transact-SQL (2005)

Need to properly implement conditional sub-query

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-07-09 : 15:13:33

(This query may not be the most optimal, so I am up to suggestions, but it is doing the job right now.)

I need to properly implement the following subquery. The real problem lies when the table is generated from this SELECT statement. Any rows that do not have a matching airport_id are simply NULL.

My preferred and intended result is to have the gathered information up to that, not NULL.

Here is the SELECT statement:


SELECT sl.sl_id AS supplierlocation_id, sl.name AS location_name, sl.code AS location_code, 
	sl.airport_location AS IsAirportLocation, sl.location_hours, sl.airport_id, 
	sl.supplier_id, sl.shuttle_type_id, sla.address_type_id, 
	sla.address, sla.address2, sla.city_id, 
	sla.zipcode_id, slp.phone_number, slp.phone_ext, 
	slp.phone_type_id, 
	((SELECT TOP 1 c.[city_name] FROM [dbo].[city] c WHERE c.[city_id] = sla.city_id) + 
	', ' + 
	(SELECT TOP 1 s.[state_name] FROM [dbo].[states] s INNER JOIN [dbo].[zipcode] z ON z.[state_id] = s.[state_id] WHERE z.zipcode_id = sla.zipcode_id) + 
	' (' + 
	(SELECT TOP 1 s.[state_abbr] FROM [dbo].[states] s INNER JOIN [dbo].[zipcode] z ON z.[state_id] = s.[state_id] WHERE z.zipcode_id = sla.zipcode_id) + 
	') ' + 
	(SELECT TOP 1 a.[airport_name] + ' ' + UPPER(a.[airport_code]) FROM [dbo].[airport] a WHERE a.[airport_id] = sl.airport_id)) AS SearchString
FROM [dbo].[supplier_location] sl INNER JOIN
	[dbo].[supplier_location_address] sla ON 
	sl.[sl_id] = sla.[location_id] INNER JOIN
	[dbo].supplier_location_phone slp ON sl.sl_id = slp.location_id
WHERE (sl.IsDeleted = 0) AND (sla.IsDeleted = 0) AND (slp.IsDeleted = 0)

I tried an IF(condition, true, false), but I was not able to get it to work right.


SELECT sl.sl_id AS supplierlocation_id, sl.name AS location_name, sl.code AS location_code, 
	sl.airport_location AS IsAirportLocation, sl.location_hours, sl.airport_id, 
	sl.supplier_id, sl.shuttle_type_id, sla.address_type_id, 
	sla.address, sla.address2, sla.city_id, 
	sla.zipcode_id, slp.phone_number, slp.phone_ext, 
	slp.phone_type_id, 
	((SELECT TOP 1 c.[city_name] FROM [dbo].[city] c WHERE c.[city_id] = sla.city_id) + 
	', ' + 
	(SELECT TOP 1 s.[state_name] FROM [dbo].[states] s INNER JOIN [dbo].[zipcode] z ON z.[state_id] = s.[state_id] WHERE z.zipcode_id = sla.zipcode_id) + 
	' (' + 
	(SELECT TOP 1 s.[state_abbr] FROM [dbo].[states] s INNER JOIN [dbo].[zipcode] z ON z.[state_id] = s.[state_id] WHERE z.zipcode_id = sla.zipcode_id) + 
	') ' + 
	(IF(SELECT TOP 1 a.[airport_name] + ' ' + UPPER(a.[airport_code]) FROM [dbo].[airport] a WHERE a.[airport_id] = sl.airport_id) <> '', '', a.[airport_name] + ' ' + UPPER(a.[airport_code]))) AS SearchString
FROM [dbo].[supplier_location] sl INNER JOIN
	[dbo].[supplier_location_address] sla ON 
	sl.[sl_id] = sla.[location_id] INNER JOIN
	[dbo].supplier_location_phone slp ON sl.sl_id = slp.location_id
WHERE (sl.IsDeleted = 0) AND (sla.IsDeleted = 0) AND (slp.IsDeleted = 0)

Does anyone have any ideas as to how to get this to work the way I wanted it to? I can offer more info if needed (table structure). I am hoping that this query would be enough to get the idea of what I need to do and have tried.

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

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-07-09 : 15:56:21

Replace the IF with IsNull(a.airport_name, '')

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-07-09 : 16:16:17

Excellent! I was hoping that it would be something that easy. I appreciate it!

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