1

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')
1
  • SQL Server? MySQL?
    – gbn
    Nov 24 '11 at 21:03
2

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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