Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

RE: Selecting data with Multiple AND Statements

Posted on 2010-11-29
12
Medium Priority
?
286 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 70

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 70

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

636 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