Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Trying to build a simple SQL query... I have a table filled with "Yes" and "No"

I want a view that only shows me records that contain "No"

SELECT PhysicalBuild.Name AS Name,
  PhysicalBuild.Labels AS Labels,
  PhysicalBuild.DNSDRAC AS DNSDRAC,
  PhysicalBuild.DNSHOST AS DNSHOST,
  PhysicalBuild.PRTGDRAC AS PRTGDRAC,
  PhysicalBuild.PRTGHOST AS PRTGHOST,
  PhysicalBuild.LouKVM AS LouKVM,
  PhysicalBuild.SwitchPorts AS SwitchPorts,
  PhysicalBuild.FiberSwitch AS FiberSwitch
FROM PhysicalBuild 

But I want it to be WHERE * = "No" or something to that effect.... my syntax is incorrect. anyone?

EDIT: I came up w/ this. Is there a cleaner/easier way to handle this? How about a way to change the records that contain "Yes" into NULL so they aren't displayed? So only the No's are shown?

SELECT PhysicalBuild.Name AS Name,
  PhysicalBuild.Labels AS Labels,
  PhysicalBuild.DNSDRAC AS DNSDRAC,
  PhysicalBuild.DNSHOST AS DNSHOST,
  PhysicalBuild.PRTGDRAC AS PRTGDRAC,
  PhysicalBuild.PRTGHOST AS PRTGHOST,
  PhysicalBuild.LouKVM AS LouKVM,
  PhysicalBuild.SwitchPorts AS SwitchPorts,
  PhysicalBuild.FiberSwitch AS FiberSwitch
FROM PhysicalBuild
WHERE (PhysicalBuild.Labels = 'No') OR
  (PhysicalBuild.DNSDRAC = 'No') OR                     
  (PhysicalBuild.DNSHOST = 'No') OR
  (PhysicalBuild.PRTGDRAC = 'No') OR
  (PhysicalBuild.PRTGHOST = 'No') OR
  (PhysicalBuild.LouKVM = 'No') OR
  (PhysicalBuild.SwitchPorts = 'No') OR
  (PhysicalBuild.FiberSwitch = 'No')
share|improve this question
    
SQL Server? MySQL? –  gbn Nov 24 '11 at 21:03
add comment

1 Answer 1

up vote 2 down vote accepted

What you have is the simplest and most straightforward.

  • your WHERE will require every column mentioned somewhere
  • changing YES into NULL requires a function on each column

For some RDBMS you can create computed column at the table level.

ALTER TABLE PhysicalBuild
   ADD SummaryState AS 
     CASE WHEN Labels = 'No' AND 
        DNSDRAC = 'No' AND                
        DNSHOST = 'No' AND 
        PRTGDRAC = 'No' AND 
        PRTGHOST = 'No' AND 
        LouKVM = 'No' AND 
        SwitchPorts = 'No' AND 
        FiberSwitch = 'No'
     THEN 'No' ELSE 'Yes' END

Then your query is

...
FROM PhysicalBuild
WHERE SummaryState = 'No'

Note: you don't need aliases the same as the column name...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.