Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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?
  • 4
  • 2
  • 2
  • +4
1 Solution
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  
smileyMilesAuthor Commented:
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'))

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

smileyMilesAuthor Commented:
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.
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)

Fahad PathanTeam LeaderCommented:
try using this statement.

Select mdu_acc from table
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
Scott PletcherSenior DBACommented:
SELECT mdu_acc
FROM tablename
GROUP BY mdu_acc
HAVING (MAX(CASE WHEN (chk_typ = 22) THEN 1 ELSE 0 END) = 1
      AND MAX(CASE WHEN (itm_num = 5) THEN 1 ELSE 0 END) = 1
      AND MAX(CASE WHEN (dta_val = '300UNK') THEN 1 ELSE 0 END) = 1)
OR (MAX(CASE WHEN (chk_typ = 22) THEN 1 ELSE 0 END) = 1
      AND MAX(CASE WHEN (itm_num = 75) THEN 1 ELSE 0 END) = 1
      AND MAX(CASE WHEN (dta_val = 'CONCRETE') THEN 1 ELSE 0 END) = 1)
ORDER BY mdu_acc
smileyMilesAuthor Commented:
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.
Scott PletcherSenior DBACommented:
>> 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 :-) .
smileyMilesAuthor Commented:
Your solution worked fine, I've given you the points.


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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