Try to find a specific values in a table that can exist in Field1 or Field2 or Field3...(and so on)

Question

I have a list of phones that I have to see if they exist in the table "Phones" the problem is that there is more than one field that each value can be in the table, i.e.

This value: 4164553627 can be in Field1 or in Field2 or Field3 or Field4... or Field25

This value: 9054558557 can be in Field1 or in Field2 or Field3 or Field4... or Field25

Is there a query than can tell me in which field the value is?

Answer 1

This will only display one answer, (and if value can be in more than one field, it will only display the first field it finds the value in, not all of them... But it should give you a start ...

Select 
   Case '4164553627'
     When Field1 then 'F1'
     When Field2 then 'F2'
     ...
     When Field23 then 'F23'
     When Field24 then 'F24'
     When Field25 then 'F25' End as Field
From Table

Answer 2

In MySQL:

SELECT  (field1 = '4164553627') |
        (field2 = '4164553627') << 1 |
        (field3 = '4164553627') << 2
        AS bitmap
FROM    mytable
HAVING  bitmap > 0

This will return you a bitmap of fields with the bits set for the fields holding the value.

Answer 3

I've used this for the same problem before. It's a little brutal, but it calculates the occurrence of the number in each field matched to a second table.

SELECT Phone, SUM(F1) AS F1_COUNT, SUM(F2) AS F2_COUNT, ... SUM(F25) AS F25_COUNT
FROM (
  SELECT Table2.Number AS Phone, COUNT(Field1) AS F1, 0 AS F2, ... 0 AS F25
  FROM Table, Table2
  WHERE Field1 = Number
  UNION
  SELECT Table2.Number AS Phone, 0 AS F1, COUNT(Field2) AS F2, ... 0 AS F25
  FROM Table, Table2
  WHERE Field1 = Number
  UNION
  .
  .
  .
  SELECT Table2.Number AS Phone, 0 AS F1, 0 AS F2, ... COUNT(Field25) AS F25
  FROM Table, Table2
  WHERE Field1 = Number
)
GROUP BY Phone, F1_COUNT, F2_COUNT, ... F25_COUNT

Answer 4

select decode(Field1,4164553627,'Field1',decode(Field2,4164553627,'Field2', decode(field3 .. 25)) ) from Table