Complex Query Problem - 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

Transact-SQL (2000)

Complex Query Problem

Author

Topic

mrsaif
Starting Member

43 Posts

Posted - 2006-03-23 : 08:20:21

I have a table "tbl_programs" which have following columns
program_id .....
1
2
3
.
.
.
another table tbl_prog_match_criteria which have following data
Cri_id | program_id | is_Same_zip_Code | is_same_country | is_same_state
1 -- 1 -- 1 -- 1 -- 0
2 -- 2 -- 0 -- 1 -- 1
3 -- 3 -- 1 -- 0 -- 0
. -- . -- . -- . -- .
. -- . -- . -- . -- .
. -- . -- . -- . -- .
--------------------------------------------------
tbl_locations
loc_id | state | zip_Code | country
1 AL 33332 US
2 AL 33332 US
3 CO 55522 US
. . . .
-----------------------------------------------
"tbl_shops"
shop_id | loc_id | program_id
1 --- 1 --- 1
2 --- 1 --- 2
3 --- 2 --- 1
. . .
----------------------------------------------------------
I want to select all the shop Ids for a perticular program that match the matching criteria in one query
Means for program_id=1 there is criteria_id= 1 whose same_zip =1 same_courtry=1 and same_State=0
I want to select all the shop_ids whose state='Al' country = 'US' and state=[any thing] in a single query or at leaset not using cursors. right now i am using coursor in my query. it slows down my query.

Muhammad Saifullah

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-23 : 09:54:00

This one is funky enough where it will be a lot faster if you follow the instructions in this link.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

thanks

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-03-23 : 11:31:55

I'm not sure if I 100% understand what you are trying to do, but maybe something like this would work?

DECLARE @State VARCHAR(2)
DECLARE @Country VARCHAR(2)

SET @State = 'AL'
SET @Country = 'US'

SELECT 
	*
FROM
	 tbl_shops s
INNER JOIN
	tbl_locations l
	ON s.loc_id = l.loc_id
WHERE
	(l.state = @State OR @State IS NULL)
	AND (l.country = @Country OR @Country IS NULL)

-Lamprey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources