?
Solved

RE: Selecting data with Multiple AND Statements

Posted on 2010-11-29
12
Medium Priority
?
285 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:smileyMiles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +4
12 Comments
 
LVL 2

Expert Comment

by:svalekar
ID: 34236159
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  
0
 

Author Comment

by:smileyMiles
ID: 34236189
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.
0
 
LVL 39

Expert Comment

by:appari
ID: 34236217
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'))

0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

Author Comment

by:smileyMiles
ID: 34236236
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.
0
 
LVL 39

Expert Comment

by:appari
ID: 34236274
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.
0
 
LVL 11

Expert Comment

by:yuching
ID: 34236710
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)

0
 
LVL 2

Expert Comment

by:Fahad Pathan
ID: 34236728
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
0
 
LVL 18

Expert Comment

by:deighton
ID: 34237347
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
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 34241553
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
0
 

Author Comment

by:smileyMiles
ID: 34253014
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34262624
>> 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 :-) .
0
 

Author Comment

by:smileyMiles
ID: 34262650
Your solution worked fine, I've given you the points.

Miles
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question