CODEWITHSUNDEEP
sql-server
ziggy
ziggy

Reputation: 1538

sql server select rows that have 1 value in ALL columns

I have a table that I want to find out if certain columns all contain the word `Compliant'

the columns are all varchar.

this query selects any row that has Compliant in any of the columns. 

SELECT  curb_reveal_check,
    ramp_running_slope_counter_slope_check,
    curb_defective_check,
    sidewalk_defective_check,
    ponding_check,
    crosswalk_location_check,
    gutter_slope_check,
    flare_slope_check,
    ramp_cross_slope_check,
    ramp_width_check,
    obstacles_check,
    dws_check,
    ramp_running_slope_check,
    apex_turning_space_check,
    cut_through_check,
    landing_length_check,
    landing_width_check,
    landing_slope_check,
    landing_check FROM table    
where 'Compliant' in(ramp_running_slope_counter_slope_check,
    curb_defective_check,
    sidewalk_defective_check,
    ponding_check,
    crosswalk_location_check,
    gutter_slope_check,
    flare_slope_check,
    ramp_cross_slope_check,
    ramp_width_check,
    obstacles_check,
    dws_check,
    ramp_running_slope_check,
    apex_turning_space_check,
    cut_through_check,
    landing_length_check,
    landing_width_check,
    landing_slope_check,
    landing_check)

but I need them all to contain Compliant

I tried substituting all for in but I got this error 

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'all'.

Upvotes: 1

Views: 1686

Answers (5)

Uueerdo
Uueerdo

Reputation: 15941

For a minor variation on Sean's answer, you could use a subquery to make the search string literal (parameter) a little less repeated:

SELECT t.stuff
FROM theTable AS t
INNER JOIN (SELECT 'Complaint' AS searchString) AS s 
ON t.field1 = s.searchString
   AND t.field2 = s.searchString
   AND t.field3 = s.searchString
   AND t.field4 = s.searchString
   ...
;

Parameterized version:

SELECT t.stuff
FROM theTable AS t
INNER JOIN (SELECT ? AS searchString) AS s 
ON t.field1 = s.searchString
   AND t.field2 = s.searchString
   AND t.field3 = s.searchString
   AND t.field4 = s.searchString
   ...
;

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74595

As another curio, you could UNPIVOT and look for rows that have a compliancy count of 18:

WITH unpiv as
SELECT SOME_ID_COLUMN, CheckType, Compliancy  
FROM   
(
    SELECT 
        SOME_ID_COLUMN, 
        curb_reveal_check,
        ramp_running_slope_counter_slope_check,
        curb_defective_check,
        sidewalk_defective_check,
        ponding_check,
        crosswalk_location_check,
        gutter_slope_check,
        flare_slope_check,
        ramp_cross_slope_check,
        ramp_width_check,
        obstacles_check,
        dws_check,
        ramp_running_slope_check,
        apex_turning_space_check,
        cut_through_check,
        landing_length_check,
        landing_width_check,
        landing_slope_check,
        landing_check   
    FROM table
) p  
UNPIVOT  
   (Compliancy FOR CheckType IN   
      (
        curb_reveal_check,
        ramp_running_slope_counter_slope_check,
        curb_defective_check,
        sidewalk_defective_check,
        ponding_check,
        crosswalk_location_check,
        gutter_slope_check,
        flare_slope_check,
        ramp_cross_slope_check,
        ramp_width_check,
        obstacles_check,
        dws_check,
        ramp_running_slope_check,
        apex_turning_space_check,
        cut_through_check,
        landing_length_check,
        landing_width_check,
        landing_slope_check,
        landing_check
    )  
)AS unpvt

SELECT SOME_ID_COLUMN 
FROM unpiv
WHERE compliancy = 'Compliant'
GROUP BY SOME_ID_COLUMN
HAVING COUNT(*) = 18

I'd assuming there is some other column that would function as an ID, to actually make it useful...

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

You have to be explicit here.

WHERE ramp_running_slope_counter_slope_check = 'Compliant'
    AND curb_defective_check = 'Compliant'
    AND sidewalk_defective_check = 'Compliant'
    AND ponding_check = 'Compliant'
    etc...

Upvotes: 2

Caius Jard
Caius Jard

Reputation: 74595

Here's a way I wouldn't recommend (but see the end of the answer for one that I would):

SELECT  curb_reveal_check,
    ramp_running_slope_counter_slope_check,
    curb_defective_check,
    sidewalk_defective_check,
    ponding_check,
    crosswalk_location_check,
    gutter_slope_check,
    flare_slope_check,
    ramp_cross_slope_check,
    ramp_width_check,
    obstacles_check,
    dws_check,
    ramp_running_slope_check,
    apex_turning_space_check,
    cut_through_check,
    landing_length_check,
    landing_width_check,
    landing_slope_check,
    landing_check FROM table    
where REPLACE(CONCAT(ramp_running_slope_counter_slope_check,
    curb_defective_check,
    sidewalk_defective_check,
    ponding_check,
    crosswalk_location_check,
    gutter_slope_check,
    flare_slope_check,
    ramp_cross_slope_check,
    ramp_width_check,
    obstacles_check,
    dws_check,
    ramp_running_slope_check,
    apex_turning_space_check,
    cut_through_check,
    landing_length_check,
    landing_width_check,
    landing_slope_check,
    landing_check), 'Compliant' ,'') = ''

Your columns would need to be 'Compliant' or something else (like 'noncompliant'). If noncompliancy is indicated by blank, it'll fail

You could also:

SELECT  curb_reveal_check,
    ramp_running_slope_counter_slope_check,
    curb_defective_check,
    sidewalk_defective_check,
    ponding_check,
    crosswalk_location_check,
    gutter_slope_check,
    flare_slope_check,
    ramp_cross_slope_check,
    ramp_width_check,
    obstacles_check,
    dws_check,
    ramp_running_slope_check,
    apex_turning_space_check,
    cut_through_check,
    landing_length_check,
    landing_width_check,
    landing_slope_check,
    landing_check FROM table    
where CONCAT(ramp_running_slope_counter_slope_check,
    curb_defective_check,
    sidewalk_defective_check,
    ponding_check,
    crosswalk_location_check,
    gutter_slope_check,
    flare_slope_check,
    ramp_cross_slope_check,
    ramp_width_check,
    obstacles_check,
    dws_check,
    ramp_running_slope_check,
    apex_turning_space_check,
    cut_through_check,
    landing_length_check,
    landing_width_check,
    landing_slope_check,
    landing_check)
      = 'CompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliantCompliant'

I would still recommend Sean's route in preference to either of these; calling functions on every row in a table is not cool.

All that said

..if you have a value of 'NonCompliant' or similar you could invert the logic:

SELECT  curb_reveal_check,
    ramp_running_slope_counter_slope_check,
    curb_defective_check,
    sidewalk_defective_check,
    ponding_check,
    crosswalk_location_check,
    gutter_slope_check,
    flare_slope_check,
    ramp_cross_slope_check,
    ramp_width_check,
    obstacles_check,
    dws_check,
    ramp_running_slope_check,
    apex_turning_space_check,
    cut_through_check,
    landing_length_check,
    landing_width_check,
    landing_slope_check,
    landing_check FROM table    
where 'NonCompliant' NOT IN (ramp_running_slope_counter_slope_check,
    curb_defective_check,
    sidewalk_defective_check,
    ponding_check,
    crosswalk_location_check,
    gutter_slope_check,
    flare_slope_check,
    ramp_cross_slope_check,
    ramp_width_check,
    obstacles_check,
    dws_check,
    ramp_running_slope_check,
    apex_turning_space_check,
    cut_through_check,
    landing_length_check,
    landing_width_check,
    landing_slope_check,
    landing_check)

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Here a "oddball" approach just for fun

Where concat(
             ramp_running_slope_counter_slope_check,
             curb_defective_check,
             sidewalk_defective_check,
             ponding_check,
             crosswalk_location_check,
             gutter_slope_check,
             flare_slope_check,
             ramp_cross_slope_check,
             ramp_width_check,
             obstacles_check,
             dws_check,
             ramp_running_slope_check,
             apex_turning_space_check,
             cut_through_check,
             landing_length_check,
             landing_width_check,
             landing_slope_check,
             landing_check
            ) = replicate('Compliant',18)

Upvotes: 1

Related Questions