How should i select - 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)

How should i select

Author

Topic

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-03-24 : 05:24:00

I have a ZipCode,state and country.
ZipCode='638011' and state='Al' and country='USA'

I have a table 'tbl_shops' which contain
Shop_Id,Program_Id,Location_Id

I have a table 'tbl_program_matching_criteria' which contain
Program_Id,Same_ZipCode of bit type,Same_Country bit type and Same State of bit type.

I have a table 'tbl_location' which contain
Location_Id,Zip_Code,State,Country
Now i need to select all the shops who match with location's zip_code,state,county if it is set to true in 'tbl_program_matching_criteria'

Currently i am using a cursor which is doing the things fine but making my stored procedure very slow.Here is code

"Declare Cur_SameZipCode cursor forward_only for
select distinct Location_Id, Program_Id from @tbl_shops_Loop
Open Cur_SameZipCode
fetch next from Cur_SameZipCode into @Location_Id, @Program_id
While @@Fetch_Status=0
Begin
Select @Match_Zip_Code=Zip_Code,@Match_State=State,@Match_Country=Country from tbl_client_locations where location_Id=@Location_Id
Select @Same_Zip_Code=Same_Zip_Code,@Same_State=Same_State,@Same_Country=Same_Country
from tbl_program_matching_criteria where Program_id=@Program_id
print @Location_Id
if @Same_Zip_Code=1
if @Match_Zip_Code<>@Shopper_Zip_Code
Begin
Delete from @tbl_shops where location_id=@Location_id and program_id=@Program_Id
End
if @Same_State=1
Begin
if @Match_State<>@Shopper_State
Begin
Delete from @tbl_shops where location_id=@Location_id and program_id=@Program_Id
End
End
if @Same_Country=1
Begin
if @Match_Country<>@Shopper_Country
Begin
Delete from @tbl_shops where location_id=@Location_id and program_id=@Program_Id
End
End
fetch next from Cur_SameZipCode into @Location_ID,@Program_Id
End
close Cur_SameZipCode"

I want state the example data of my table too.
tbl_shops
shop_id location_id program_id
1 1 1
2 2 1
3 3 2
tbl_locations
Location_id ZipCode State Country
1 333332 AL USA
2 638431 AL USA
tbl_program_matching_criteria
Program_Id same_ZipCode Same_Country Same_State
1 1 1 0
2 0 1 1
3 1 0 0

Now I want is the query which can select "shops" from tbl_shops that matches the zip_code,state,country of locations if bit of relative zip_code,state,country is set to 1.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 10:22:22

So, what would your output look like for your example data?

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-03-24 : 10:39:28

My Output would like all 'shops' from 'tbl_shops' to whom the zipcode,country,state has matched if required in program matching criteria like this
Output
shop_id location_id program_id
1 1 1
2 1 2

I mean the shops will be filtered that don't match the ZipCode='638011' and state='Al' and country='USA' if in program matching criteria their bits are set on
Regards

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 10:58:07

In that case, you got me. Why does the location_id of shop_id 2 change from '2' in the data to '1' in the output?

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-03-24 : 11:10:01

Sorry that is just the example of data. The main purpose is to filter the data that i described above. There is no change in location_id.
If You want to see that then it should like
Shop_Id Location_Id Program_Id
1 1 1
2 2 1

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 11:21:12

Can you give an example that works? That's surely very easy for you, and will make it so much easier for us to help you. As far as I can work out, the example you've given should result in no rows - not 2 rows. Is that not correct?

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 11:26:07

I should wait for a proper example, but I've taken a stab without one, so this may not be correct...

--data
declare @tbl_shops table (shop_id int, location_id int, program_id int)
insert @tbl_shops
          select 1, 1, 2 --note changed to 2
union all select 2, 2, 2 --note changed to 2
union all select 3, 3, 2

declare @tbl_locations table (Location_id int, ZipCode varchar(10), State varchar(2), Country varchar(10))
insert @tbl_locations
          select 1, '333332', 'AL', 'USA'
union all select 2, '638431', 'AL', 'USA'

declare @tbl_program_matching_criteria table (Program_Id int, same_ZipCode bit, Same_Country bit, Same_State bit)
insert @tbl_program_matching_criteria
          select 1, 1, 1, 0
union all select 2, 0, 1, 1
union all select 3, 1, 0, 0

--inputs
declare @ZipCode varchar(10)
declare @State varchar(2)
declare @Country varchar(10)

set @ZipCode = '638011'
set @State = 'AL'
set @Country = 'USA'

--select the required output
select s.*
from @tbl_shops s
     inner join @tbl_program_matching_criteria c on s.program_id = c.program_id
     left outer join @tbl_locations l on s.Location_id = l.Location_id
where
     (same_ZipCode = 0 or (same_ZipCode = 1 and ZipCode = @ZipCode)) and
     (Same_Country = 0 or (Same_Country = 1 and Country = @Country)) and
     (Same_State   = 0 or (Same_State   = 1 and State   = @State  ))

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-03-24 : 11:33:10

Ok see the example
tbl_client_locations
location_id Zip_Code State Country
1 33332 AL USA
2 63108 NY USA

tbl_program_matching_criteria
Program_Id Same_ZipCode Same_State Same_Country
1 1 1 1

tbl_shops
Shop_Id Location_Id Program_Id
1 1 1
2 2 1

Now matching criterias are match zipcode,country and state
if I have my zipcode='33332' and State='AL' and Counry='USA'
then this shop will show to me the shop_id 2 will not show to me.
Result is
tbl_shops
Shop_Id Location_Id Program_Id
1 1 1

If My Matching Critieria of Changes
tbl_program_matching_criteria
Program_Id Same_ZipCode Same_State Same_Country
1 1 0 0
Then Only Same_ZipCode of the location will be checked
and if i have my zipcode='33332' and State='NY' and Counry='UK'
now state and country does not matter to him.
If all three bits are zero then location's zip_code,country and state would not be checked.

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-03-24 : 11:37:45

I am checking your code..........

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-03-24 : 13:21:02

Thanx "RyanRandall" Thank you very much you have solved my great problem.
But I request you to solve my another problem.
I have another fields in "tbl_program_matching_criteria" too.
For Example i have height field which can contain (>5,<5,>=5,=5,<=5,<5) here 5 is variable.
and i have my height as shopper_height=5
if my length is 5 and matching criteria it resides >5 then shop_id does not qualify. if resides=5 then shop_id qualifies.

Tbl_Program_Matching_Criteria

Program_Id Height
1 >5-2
after "-" height is in inches. both have to be matched.
If any confusion please ask.
Thanks agin.
Regards

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 13:30:14

Thanks muzaffar

You should know by now what I'm going to ask for before I try to solve anything for you...

Can you give a full (i.e. with all the relevant tables, and with the outputs you'd want) example illustrating what you want to do.

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-03-24 : 14:21:18

I have table "tbl_program_matching_criteria" which have fields
Program_Id Same_Zip_Code Same_State Same_Country Height Weight Shopper_Income_Id
1 1 1 1 >5-2 50 1
2 0 0 0 null 1
in the first program_id i will need to filter the shops with same city,state and zip_code that i have done now i need to check height,weight and shopper_income_id for the shops to show the shopper whose height,weight and incom_id i have in variables like @shopper_height,@shopper_weight,@shopper_income_id
Now i can check icome_id with the query you wrote above but i think i will face problem in matching height that has >,>=,<,<=,= and then height in feet-inches >=5-3 for example.
Now if shopper's height is 5-2 that i have a variables shopper_height='5-2' and this shopper is not >= to 5-3. But if SHopper_Height='5-4' and it is greater than '5-3' that of matching criteria then he qualifies to the shop.
Thanks for your replies.
Regrads

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources