not equals

[sammybee77]

Please could someone look at my code below and tell me why it is returning all the ones where sk_code_id = 2618

select distinct sk_id
from sk, people, business
where sk_code_id <> 2618
and sk_id = peop_id
and (peop_bs = bs_id)
and (bs_name like 'ABC LTD')
and (peop_type = 'C')

Many thanks

Sam

 

[karluk]

It seems likely that you have multiple sk_code_id values per sk_id. The non 2618 values of sk_code_id cause the sk_id to be included even when you are explicitly excluding them in your where clause. For example, suppose your tables contain the following two sets of values. The first set would be excluded, but the second would conform to your where clause and cause sk_id = 'xyz' to be included in your result set.

sk_code_id = 2618
sk_id = peop_id = 'xyz'
peop_bs = bs_id = 'abc'
bs_name like 'ABC LTD'
peop_type = 'C'


sk_code_id = 1234
sk_id = peop_id = 'xyz'
peop_bs = bs_id = 'abc'
bs_name like 'ABC LTD'
peop_type = 'C'

 

[Turkbear]

Try adding parens around all the where clause -
this will cause it to be evaluated in toto before the selection is made:

select distinct sk_id
from sk, people, business
where [red]( [/red]sk_code_id <> 2618
and sk_id = peop_id
and (peop_bs = bs_id)
and (bs_name like 'ABC LTD')
and (peop_type = 'C')[red])[/red]

 

[sammybee77]

Hi,

Sorry this isn't working. Karluk - I can't state that the peop_id is equal to anything as I'm trying to find those that don't contain a sk_code_id = 2618. Turkbear - I added the extra brackets but to no avail - any more ideas?

Many thanks

Sam

 

[Farab]

How do you know it is not returning the correct values? You're selecting sk_id, but the where clause refers to sk_code_id. What is the relationship in data between sk_id and sk_code_id.

Which tables contain the column sk_id or sk_code_id?

Regards.

 

[sammybee77]

Hi Farab,

The criteria I am selecting minus the sk_code_id not equal to 2618 should give me 3 out of 15(that meet the rest of the criteria) rows instead I get a result of 12 all of which are = to 2618.

sk is the table which holds both sk_id and sk_code_id. sk_id is the link to the people table. Please help!

Thanks

Sam

 

[karluk]

Would you mind performing an experiment? Try running the same query, but add sk_code_id to the list of columns being selected. That will give us concrete evidence of whether the sk_code_ids of 2618 are really being selected.

select distinct sk_id, sk_code_id
from sk, people, business
where sk_code_id <> 2618
and sk_id = peop_id
and (peop_bs = bs_id)
and (bs_name like 'ABC LTD')
and (peop_type = 'C')

 

[JigarMD]

is ur field of varchar/char datatype , if yes try adding sqingle quotes in where clause