We help IT Professionals succeed at work.

Filtering results with SQL Server and ASP

Medium Priority
192 Views
Last Modified: 2010-08-05
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')
Comment
Watch Question

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

Author

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

Author

Commented:
i am afraid so!
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?

Author

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?
CERTIFIED EXPERT
Top Expert 2011
Commented:
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...


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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?

Author

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?

Author

Commented:
any ideas??
could you post both table structures? and the data record(s) that are giving you trouble

Author

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  
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.