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')
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')
ASKER
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?
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$cont
WHERE (Hs$Contact.[Main Member Code] = MMColParam) AND
(Hs$Contact.[Status Code] = 'A')
same result?
FROM dependant_type join Hsd$Contact on dependant_type.id=hsd$cont
WHERE (Hs$Contact.[Main Member Code] = MMColParam) AND
(Hs$Contact.[Status Code] = 'A')
same result?
ASKER
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?
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?
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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?
ASKER
any ideas??
could you post both table structures? and the data record(s) that are giving you trouble
ASKER
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.[depen
WHERE c.[Main Member Code] = MMColParam
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.[depen
WHERE c.[Main Member Code] = MMColParam
what happens if you do
Hs$Contact.[Status Code] not in ('L', 'D')
or
Hs$Contact.[Status Code] in ('A')