Solved

SQL Where Clause Not Working

Posted on 2014-04-21
14
259 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

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.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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