[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Filtering results with SQL Server and ASP

i have got the following query which works to a point, except the very last part is not filtering out correctly.  Let me explain, the last part Hs$Contact.[Status Code]  = 'A' is filtering out people with other letters e.g. L is left, D is deceased, the A is for active, however it returns all records including people with status code L and D

Any ideas on how to tweak it to work right?

SELECT dependant_type.*, Hs$Contact.*
FROM dependant_type, Hsd$Contact
WHERE (dependant_type.[ID] = Hs$Contact.[dependant type])  AND  (Hs$Contact.[Main Member Code] = MMColParam) AND (Hs$Contact.[Status Code]  = 'A')
0
ckawebcreation
Asked:
ckawebcreation
  • 7
  • 4
1 Solution
 
derekkrommCommented:
it looks right to me

what happens if you do

Hs$Contact.[Status Code] not in ('L', 'D')
or
Hs$Contact.[Status Code] in ('A')
0
 
ckawebcreationAuthor Commented:
hi derek,

it still brings the all the results back, there is definitely one of the records with a status code L, there is something amiss somewhere, maybe to do with the earlier parts querying 2 tables?
0
 
derekkrommCommented:
SELECT dependant_type.*, Hs$Contact.*
FROM dependant_type join Hsd$Contact on dependant_type.id=hsd$contact.[dependant type]
WHERE (Hs$Contact.[Main Member Code] = MMColParam) AND
(Hs$Contact.[Status Code]  = 'A')

same result?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ckawebcreationAuthor Commented:
i am afraid so!
0
 
derekkrommCommented:
What returns if you do

select distinct [status code] from hs$contact

and also do

select distinct [status code] from hs$contact where [status code] not in ('D', 'L')

also

What is the data type of status code?
0
 
ckawebcreationAuthor Commented:
there could be something else wrong actually, i have just bound the status code to the page and they are all coming back with A, yet in the sql server database one of them has an L under status code?
0
 
LowfatspreadCommented:
SELECT dependant_type.*, c.*
FROM dependant_type
Inner join (select * from Hsd$Contact   Where Hs$Contact.[Status Code]  = 'A') as c
    on dependant_type.id=c.[dependant type]
WHERE c.[Main Member Code] = MMColParam


what is the relationship between dependant_type and Contact?

give use some smaple data and the results you get and those you desire...

please explain what you are trying to achieve...


0
 
ckawebcreationAuthor Commented:
hi lowfatspread,

dependant type has a description which is not in contact, contact has a numeric value, e.g. 1,2,3 which relates to the primary key in dependant_type, this table holds the description I put on the page, that is why i need both tables.  Also the status code states if they have Left, Deceased or are Active, that is why I need to filter everybody who does not have a status code of L.  It works fine but just does not filter the non L's  

Do you understand what I am trying to do?
0
 
ckawebcreationAuthor Commented:
i just tried it by querying the contact table and the first record came back with a status code of L, but when we use both tables it comes back as A?

0
 
ckawebcreationAuthor Commented:
any ideas??
0
 
derekkrommCommented:
could you post both table structures? and the data record(s) that are giving you trouble
0
 
ckawebcreationAuthor Commented:
i must be the most dumbest person on the planet! i did not have the right connection selected! Doh!  so the code that was pasted days ago was right!

Lowfatspreasd code works perfectly with the desired results - cheers

SELECT dependant_type.*, c.*
FROM dependant_type
Inner join (select * from Hsd$Contact   Where Hs$Contact.[Status Code]  = 'A') as c
    on dependant_type.id=c.[dependant type]
WHERE c.[Main Member Code] = MMColParam  
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now