Solved

SQL Where Clause Not Working

Posted on 2014-04-21
14
262 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
14 Comments
 
LVL 65

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 65

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 65

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 65

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 65

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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.
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

832 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