Can I run a sql query to return all 'yes' fields only from table. I have table several enum fields either 'yes' or 'no' and one called 'model'

I have before run a query based on a single enum field SELECT FROM db WHERE field1='yes' and model='xxx'. But cannot figure out how to
SELECT FROM DB WHERE ?all fields?='yes' and model='xxx'.

I have tried LIKE but no joy.

OR do I have to retrieve all and use ECHO to print fields'yes'

charles

    Hi Charles,

    You could try ...

    SELECT model, IF( field1='yes' && field2='yes', 'yes', 'no') as allfields
    FROM DB
    WHERE allfields='yes'
    AND model='xxx'

    ... I think that'll work!

    Paul 🙂

      Thanks- looks good- will try Monday as I mislaid my memstick, so could not bring pages home.
      Thanks- will mark as resolved, re-submit if i still have a problem.

        Just of course thought- fields in table are not called field 1 etc- actually things like kitch_print, weigh_scales etc ( its for a cash register database of peripherals.
        I could of course either rename fields to field 1, 2 etc, which will confuse an inputter(me), or how do I run using the real field names as above- some 20 in total.

        Sorry

        Charles

          Just use the names of the fields and 'join' the equalities together with logical ANDs (&&).

          Here's with 3 fields ...

          SELECT model, IF( kitch_print='yes' && weigh_scales='yes'&& other_value='yes', 'yes', 'no') as allfields
          FROM DB
          WHERE allfields='yes'
          AND model='xxx'

          Paul.

            That will not work. You cant use aliases in the where clause.

            You need to specify the columns in the where clause

            where 'yes' in (kitch_print,weigh_scales,
 other_value)

              As below you mean ?

              SELECT model_no FROM tillaccess WHERE 'yes' in (kitch_print, weigh_scales)

              Run in PHPmyadmin still pulls out all models, there are only 3 models with weigh_scales and kitch_print . If reduce to just weigh_scales, works okay

                This does seems to work
                SELECT model_no FROM tillaccess WHERE 'yes' in (weigh_scales) AND 'YES' in (ink_print) AND 'YES' in (bar_scan)

                  Basically, you are backed to your initial query. There is no shortcut, you must specify the individual columns.

                  I missed that you wanted that all columns to be yes.

                  a in (b,c,d)

                  is just an abbreviation of

                  a = b or a = c or a = d

                    columns have to be yes to show what till can have certain peripherals.

                    I understand the logic of 'a=yes' in (b,c,d,e,f,) but were we not there before- or am i being dense. I can of course just write as works, but seems laborious, though only once so I could live with it. a in (b,c,d) fails, unless I have misunderstood. Would a fn of $a as 'yes' help, then run $a in (b,c,d,e,f)

                      Write a Reply...