Solved

SQL Where Clause Not Working

Posted on 2014-04-21
14
266 Views
Last Modified: 2014-04-21
I am attempting to write a simple WHERE but it's not returning my results even though I know for certain the values are there.  In query below, I expect to get the 2 rows returned but it isn't.  Ughhh... it's so frustrating.

select a.*
from #temp1 a
where (a.CustomPropertyName = 'Capitalization'
and a.CustomPropertyValue = 'Small-Cap EQuities')
and (a.CustomPropertyName = 'BASAllocation'
and a.CustomPropertyValue = 'Large-Cap Equities')

Open in new window


Expected Output Based on the WHERE clause above;
Symbol         Name                      PropertyName      PropertyValue
cop         ConocoPhillips      Capitalization      Small-Cap Equities
cop         ConocoPhillips      BASAllocation      Large-Cap Equities
0
Comment
Question by:KANEDA 0149
[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
14 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40013115
You need an OR in between the two WHERE criteria, i.e. WHERE 'apples OR oranges' instead of 'WHERE apples AND oranges'

select a.*
from #temp1 a
where 
  (a.CustomPropertyName = 'Capitalization' and a.CustomPropertyValue = 'Small-Cap EQuities')

OR   -- < I think this is what you mean 

  (a.CustomPropertyName = 'BASAllocation' and a.CustomPropertyValue = 'Large-Cap Equities')

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 40013117
'Small-Cap EQuities' to 'Small-Cap Equities' ?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40013118
Try an 'or' between the two sets.
select a.*
from #temp1 a
where (a.CustomPropertyName = 'Capitalization'
and a.CustomPropertyValue = 'Small-Cap EQuities')
OR (a.CustomPropertyName = 'BASAllocation'
and a.CustomPropertyValue = 'Large-Cap Equities')

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:KANEDA 0149
ID: 40013130
Sorry, that was a typo on my part.  Even when I changed to 'Small-Cap Equities'  it still did not return my results.

When I run the where on the symbol, it returns both items but I want to find other scenarios where those criteria matched.

select a.*
from #temp1 a
where a.Symbol = 'cop'

Open in new window


I did not want to use the OR in between because I am looking to get where all 4 criteria is a match and not one or the other.  Hope that makes sense.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40013139
>I did not want to use the OR in between because I am looking to get where all 4 criteria is a match and not one or the other. Hope that makes sense.

Nope.  My name is Jim, not 'Jim AND Bob'.

Your WHERE clause references a.CustomPropertyName twice.
So ... explain to us how a single a.CustomPropertyName value can be equal to both 'Capitalization' AND 'BASAllocation'.
0
 

Author Comment

by:KANEDA 0149
ID: 40013148
LOL... The custom property name is a table where we store all custom labels which are added to a security.  One security can have x number of custom property labels assigned to it; therefore my select was referencing where the custom property name then the values associated to each label.  Did that help?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40013155
>One security can have x number of custom property labels assigned to it;
You're losing us.  

1.  Define security, as it's not in your original question.  

2.  What's the exact method in which 'x number of custom property labels' occurs.
Do you mean multiple rows?

If both are correct, and assuming a lot, then your query needs to go something like this...
SELECT Symbol, Name, PropertyName, PropertyValue
FROM YourTable
GROUP BY Symbol, Name, PropertyName, PropertyValue
HAVING COUNT(PropertyName) > 1 OR COUNT(PropertyValue) > 1

Open in new window

0
 

Author Comment

by:KANEDA 0149
ID: 40013172
Sorry, security is the record.  Each security (i.e. record) can have multiple custom properties which will return mulitple rows for each instance.

In my original example, the record returned is the security symbol 'cop' which has 2 custom properties assigned; one called "BASAllocation" and the other is "Capitalization".  For each BASAllocation custom property assigned, the value can be "Small-Cap Equities" or "Large-Cap Equities" or "International" and so forth.  The same for Capitalization custom property.

I want to return where BASAllocation = Small-Cap Equities AND where Capitalization = Large-Cap Equities only and nothing else.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40013188
I am not sure if you need additional HAVING / WHERE conditions or not.  But this will identity any symbol that has both those values, with or without other values.


SELECT *
FROM #temp1
WHERE
    Symbol IN (
        SELECT Symbol
        FROM #temp1 a
        GROUP BY Symbol
        HAVING
            SUM(CASE WHEN a.CustomPropertyName = 'Capitalization' AND
                          a.CustomPropertyValue = 'Small-Cap EQuities'
                     THEN 1 ELSE 0 END) = 1 AND
            SUM(CASE WHEN a.CustomPropertyName = 'BASAllocation' AND
                          a.CustomPropertyValue = 'Large-Cap EQuities'
                     THEN 1 ELSE 0 END) = 1
    ) AND
    CustomPropertyName IN ( 'Capitalization', 'BASAllocation' )
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40013192
Ok.  Try this and see what happens (although ScottPletcher may have a complete solution above)...

SELECT a.Symbol, a.Name
FROM #temp1  a
      JOIN (
         SELECT Symbol, Name, PropertyName, PropertyValue
         FROM #temp1
         WHERE CustomPropertyName = 'BASAllocation'
              and CustomPropertyValue = 'Large-Cap Equities') b ON a.Symbol = b.Symbol
WHERE a.CustomPropertyName = 'Capitalization' and a.CustomPropertyValue = 'Small-Cap EQuities'
0
 

Author Closing Comment

by:KANEDA 0149
ID: 40013200
Boom... that did ScottPletcher.  Thank you so much!
0
 

Author Comment

by:KANEDA 0149
ID: 40013207
Thank you too Jim!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40013230
Uhh ... Not to take away from Scott's correct answer, but next time please acknowledge that the way your original question was worded forced other experts to spend time flushing out details necessary to answer your question, that in effect you didn't give us any credit for.
0
 

Author Comment

by:KANEDA 0149
ID: 40013253
Sorry about that Jim, I will remember next time.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

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