SQL Where Clause Not Working

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
KANEDA 0149Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
awking00Commented:
'Small-Cap EQuities' to 'Small-Cap Equities' ?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Dave BaldwinFixer of ProblemsCommented:
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
 
KANEDA 0149Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
KANEDA 0149Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
KANEDA 0149Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
KANEDA 0149Author Commented:
Boom... that did ScottPletcher.  Thank you so much!
0
 
KANEDA 0149Author Commented:
Thank you too Jim!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
KANEDA 0149Author Commented:
Sorry about that Jim, I will remember next time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.