Solved

RE: Selecting data with Multiple AND Statements

Posted on 2010-11-29
12
275 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 2

Expert Comment

by:Fahad Pathan
Comment Utility
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
Comment Utility
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:
ScottPletcher earned 250 total points
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
>> 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
Comment Utility
Your solution worked fine, I've given you the points.

Miles
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

11 Experts available now in Live!

Get 1:1 Help Now