?
Solved

SQL Where Clause Not Working

Posted on 2014-04-21
14
Medium Priority
?
274 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 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 84

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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

600 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