Counting all occurancies in a table 1

[liark]

I have a table which has ten fields and any number of records. I have another table with one field and ten records. (it has more than one field really but that is not important)


I want to loop through the first table looking through its fields for each occurance of entries of the second table.

I feel this should be simple but can't get my head around it.

At the end I want a list of the ten entries in table two with a value - ie the number of times each entry occurs (in any field) from table one.

Thanks

Liark

 

[r937]

this would be so much easier if your tables were normalized

select entry
     , sum(hit) as hits
  from (
select t2.entry
     , case when t2.entry = t1.field1
            then 1 else 0 end 
          as hit
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field1
union all      
select t2.entry
     , case when t2.entry = t1.field2
            then 1 else 0 end  
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field2
union all      
select t2.entry
     , case when t2.entry = t1.field3
            then 1 else 0 end  
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field3
union all      
select t2.entry
     , case when t2.entry = t1.field4
            then 1 else 0 end  
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field4
union all      
select t2.entry
     , case when t2.entry = t1.field5
            then 1 else 0 end  
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field5
union all      
select t2.entry
     , case when t2.entry = t1.field6
            then 1 else 0 end  
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field6
union all      
select t2.entry
     , case when t2.entry = t1.field7
            then 1 else 0 end  
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field7
union all      
select t2.entry
     , case when t2.entry = t1.field8
            then 1 else 0 end  
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field8
union all      
select t2.entry
     , case when t2.entry = t1.field9
            then 1 else 0 end  
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field9
union all      
select t2.entry
     , case when t2.entry = t1.field10
            then 1 else 0 end  
  from table1  as t2
left outer
  join table1  as t1
    on t2.entry = t1.field10
       ) as dt
group
    by entry

rudy

http://r937.com/

SQL Consulting