smileyMiles
asked on
RE: Selecting data with Multiple AND Statements
Hi All,
Have a problem with using multile AND statements in an SQL 2005 query. The data is arranged in a table like this:
mdu_acc dta_val chk_typ itm_num
16433 4/03/2010 22 1
16433 3 22 2
16433 D06233 22 3
16433 300UNK 22 5
16433 7.8 22 6
16433 CONCRETE 22 7
16433 1.1 22 8
16433 100 22 9
Now, I need to extract all mdu_acc values where:
(chk_typ = 22) AND (.itm_num = 5) AND (dta_val = '300UNK'))
AND (chk_typ = 22) AND (itm_num = 7) AND (dta_val = 'CONCRETE')
So, this should = 16433.
When I run the multiple AND statement above, no results are produced. I've tried different variations of nesting the above statement, but this doesn't work either.
Any ideas?
Have a problem with using multile AND statements in an SQL 2005 query. The data is arranged in a table like this:
mdu_acc dta_val chk_typ itm_num
16433 4/03/2010 22 1
16433 3 22 2
16433 D06233 22 3
16433 300UNK 22 5
16433 7.8 22 6
16433 CONCRETE 22 7
16433 1.1 22 8
16433 100 22 9
Now, I need to extract all mdu_acc values where:
(chk_typ = 22) AND (.itm_num = 5) AND (dta_val = '300UNK'))
AND (chk_typ = 22) AND (itm_num = 7) AND (dta_val = 'CONCRETE')
So, this should = 16433.
When I run the multiple AND statement above, no results are produced. I've tried different variations of nesting the above statement, but this doesn't work either.
Any ideas?
Satish Valekar
Try to add where clause one by one Then you will come to know after which AND Condition data is not retrieving Then find out the problem
ASKER
Yeah, I kind of tried that. The problem is that all the data is stored in one table and is referenced by the chk_typ/ itm_num switch, i.e. (chk_typ = 22) AND (.itm_num = 5) , but there will be multiple AND statements. This works if you evaluate the (chk_typ = 22) AND (.itm_num = 5) AND (dta_val = '300UNK')) first, then all relevant records are returned for this switch, but add on the second statement AND (chk_typ = 22) AND (itm_num = 7) AND (dta_val = 'CONCRETE'), then nothing is returned. Think it has to do with the multiple AND's, but have run out of ideas on how to syntax the statement.
you mean this
((chk_typ = 22) AND (.itm_num = 5) AND (dta_val = '300UNK'))
OR ( (chk_typ = 22) AND (itm_num = 7) AND (dta_val = 'CONCRETE'))
((chk_typ = 22) AND (.itm_num = 5) AND (dta_val = '300UNK'))
OR ( (chk_typ = 22) AND (itm_num = 7) AND (dta_val = 'CONCRETE'))
ASKER
Well using an 'OR' on the second statement will return results, but not with those combined conditions. It returns too many records as it returns records where cond1 OR cond = true, rather than when both statements are true, as in the table example above.
how can both the statements(on the same field) be true at the sametime?
if a record has chk_typ = 22 AND .itm_num = 5 the same record cannot have itm_num = 7 so you are not getting any results back. may be we are unable to follow your question/problem.
if a record has chk_typ = 22 AND .itm_num = 5 the same record cannot have itm_num = 7 so you are not getting any results back. may be we are unable to follow your question/problem.
Perhaps you can try this
Select mdu_acc
From tablea a
Where (chk_typ = 22) AND (.itm_num = 5) AND (dta_val = '300UNK')
And exists (select 1 from tablea b Where (chk_typ = 22) AND (itm_num = 7) AND (dta_val = 'CONCRETE') And a.mdu_acc = b.mdu_acc)
Select mdu_acc
From tablea a
Where (chk_typ = 22) AND (.itm_num = 5) AND (dta_val = '300UNK')
And exists (select 1 from tablea b Where (chk_typ = 22) AND (itm_num = 7) AND (dta_val = 'CONCRETE') And a.mdu_acc = b.mdu_acc)
try using this statement.
Select mdu_acc from table
where
chk_typ in (22) AND itm_num in (5, 7) AND dta_val in ('300UNK', 'CONCRETE')
and mdu_acc = 16433
Select mdu_acc from table
where
chk_typ in (22) AND itm_num in (5, 7) AND dta_val in ('300UNK', 'CONCRETE')
and mdu_acc = 16433
the logic applies to individual rows
(itm_num = 5) AND (dta_val = '300UNK')
that means that both those conditions have to apply in order to select a row
(itm_num = 5) AND (itm_num = 7)
the second statement never selects any rows, because it is asking for both of those conditions to be true. It doesn't mean that rows having (itm_num = 5) and also rows that have (itm_num = 7) are selected
(itm_num = 5) AND (dta_val = '300UNK')
that means that both those conditions have to apply in order to select a row
(itm_num = 5) AND (itm_num = 7)
the second statement never selects any rows, because it is asking for both of those conditions to be true. It doesn't mean that rows having (itm_num = 5) and also rows that have (itm_num = 7) are selected
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all for the replies.
I tried most combinations of what was suggested. I did try the CASE statement, as ScottPletcher suggested, but wiht different syntax, which didn't work.
However ScottPletcher's solution works the best, so I have awarded him the points. Thanks for all the contributiions, was nice to see some variations.
I tried most combinations of what was suggested. I did try the CASE statement, as ScottPletcher suggested, but wiht different syntax, which didn't work.
However ScottPletcher's solution works the best, so I have awarded him the points. Thanks for all the contributiions, was nice to see some variations.
>> I did try the CASE statement, as ScottPletcher suggested, but wiht different syntax, which didn't work. <<
Please post specifically what you tried. To get what you need, you have to be careful in how you specify the conditions.
The code I wrote will find mdu_acc's that meet ALL the conditions specified in either set of the conditions in the HAVING clause, because I thought that's what you were after :-) .
Please post specifically what you tried. To get what you need, you have to be careful in how you specify the conditions.
The code I wrote will find mdu_acc's that meet ALL the conditions specified in either set of the conditions in the HAVING clause, because I thought that's what you were after :-) .
ASKER
Your solution worked fine, I've given you the points.
Miles
Miles