Solved

RE: Selecting data with Multiple AND Statements

Posted on 2010-11-29
12
278 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
  • 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
 

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 250 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

863 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now