SQL - Hallar valor más repetido por cada registro de otra tabla

Hola, planteo a continuación un problema al que no encuentro solución, y que a priori pensé que no sería tan complicado.
Pongo a continuación un ejemplo parecido con los campos imprescindibles, distinto a lo que tengo en bd que es mas complejo:

Tengo tres tablas (usuarios, alimentos, y alimentos_consumidos)

En la de usuarios tengo los siguientes campos
user_id
alimento_id (con valor null en todos los registros)

En la de alimentos
alimento_id
descripcion_alimento

En la de alimentos_consumidos
alimento_consumido_id
user_id
alimento_id

Necesito un update, que me actualice en la tabla de usuarios, el campo alimento_id mas consumido (o encontrado) por cada user_id en la tabla alimentos_consumidos.

Llevo dando vueltas y vueltas y no hallo la forma de hacerlo
¿alguna idea?

gracias de antemano.

Hola osasa:

Hay varias cosas que señalar en cuanto a tu planteamiento:

1. El que tengas en tu tabla usuarios un campo que pueda ser calculado (alimento id) es UN ABSOLUTO ERROR DE DISEÑO. Una de las primeras reglas para la normalización de tablas es que NO DEBEN EXISTIR CAMPOS QUE PUEDAN SER CALCULADOS. ¿Qué beneficio tienes al tener un campo que deba ser actualizado cada vez que cambian tus otras tablas?... en realidad no hay ninguna razón de peso que me puedas dar. Si necesitas obtener qué alimento es el que consume más veces un usuario, pues entonces haces una consulta y lo calculas, PERO POR NINGÚN MOTIVO DEBES ALMACENAR ESTE DATO EN LA TABLA. Si estuvieras estudiando Diseño de Bases de Datos y haces algo como esto, seguramente tu maestro te reprobaría... si estás trabajando en alguna empresa y planteas un modelo como este, te expondrías a que te despidieran, así de grave es el asunto.

2. No entiendo cómo están relacionadas tus tres tablas... creo que es conveniente que además de las estructuras de tus tablas pongas también algunos datos de ejemplo, para entender mejor el problema... puedo suponer que en tu tabla alimentos_consumidos el campo alimento_consumido_id es un consecutivo, mientras que los campos user_id y alimentos_id son llaves foráneas a tus tablas usuarios y alimentos.

3. Para obtener el alimento que más consume un usuario, lo puedes hacer de varias formas, depende también mucho de qué motor de BD estás utilizando... pero también hay varias consideraciones que tienes que tener, por ejemplo, ¿qué pasa cuando un usuario tiene más de un alimento con un valor máximo de consumos?, es decir, observa este pequeño ejemplo:

Supongamos que tenemos estas tablas (me baso más o menos en lo que pones como estructura de tus tablas);



Si obtenemos cuántas veces consumió cada alimento por usuario, tenemos algo como esto:


de aquí se observa que el usuario UNO, consume más veces el alimento pizza (lo consume tres veces), el usuario TRES consume más veces Ensalada (lo consume tres veces) y el usuario CUATRO consume sólo pizza, no hay más registros, pero qué pasa con el usuario DOS???

El usuario DOS consume de igual manera Pizza y Ensaladas, por lo tanto, según tu lógica de negocio, qué id alimento es el que consume mas??? poner uno y otro implicaría dar ventaja a un alimento sobre el otro, lo cual no creo que sea correcto.

Trata de aclararnos el panorama, comenta algo acerca de lo que pongo en este post y dinos qué has tratado de hacer para resolver esta consulta...

Saludos
Leo

Hola de nuevo, entiendo lo que dices. Mi problema es que estoy cargando unas tablas csv que me pasan con información muy sesgada. Yo lo único que he hecho ha sido pasarlas a una base de datos (la he creado yo a partir de la misma estructura de los csv) para poder trabajar de una manera más cómoda con la información.

La tabla que puse es de ejemplo, y los datos reales no tienen nada que ver con la alimentación. Intento ser mas concreto.
Existe una tabla de usuarios, otra de ciudades, y otra de pagos realizados.

Todo esto viene de una app, en la cual los usuarios se registran (tabla usuarios), por medio de la que pueden comprar un artículo determinado dando a que se grabe cada compra en la (tabla pagos_realizados), y en una serie de ciudades (tabla ciudades) .

Al registrase el usuario en la APP no se les obliga a localizarse ni por georeferenciación ni aportando dirección o domicilio, los usuarios pueden comprar un determinado producto en las distintas ciudades disponibles.

En los informes que realizo (con QlikView), necesito de alguna forma poder asociarle a cada usuario una "ciudad habitual" que no existe, ya que tengo que hacer una evolución de altas de usuario por ciudad (ya que se vincula esta evolución a campañas de marketing locales). La verdad es que la única opción que se me ha ocurrido con tan poca información (y mala), es la de utilizar cada registro de venta (asociado a una ciudad y al propio usuario que realiza la compra), para poder establecer una ciudad digamos "preferente", y poder tener una ciudad real a la que vincular la información por cada usuario, así que he creado yo un campo en la tabla de registros llamado "ciudad_destino", que es el que desde los informes de QlikView, tendré en cuenta para ver las evoluciones de registro de la APP.

Por supuesto que esto tiene inconvenientes, ya que no todos los usuarios que se instalan la app, la utilizan (tendré que desechar los usuarios que nunca han realizado una compra), también podrá pasar que si un usuario realizara el mismo número de compras en dos ciudades no sería objetivo en elegir alfabéticamente la ciudad (también es verdad que al ser compras locales esto es muy raro que pase), y por no decir que tendré que realizar un trigger con cada insert en la base de datos que realice en la carga de nuevos registros. El problema es el de que al no poder controlar la aplicación, y sólo poder basarme en los ficheros de carga csv que me pasan semanalmente no se me ha ocurrido otra manera.

Actualmente para solventar el problema, me he hecho un pequeño proceso en vb, que recorre cada usuario después de cargados todos los registros de ventas, y busca la ciudad que mas registros tiene, acuatizando el campo que he creado yo en la tabla de usuarios (ciudad_destino), en la que estoy cogiendo sólo el primer registro que me devuelva la select.

Voy lanzando usuario a usuario el siguiente update:

Update usuarios set ciudad_destino =
(SELECT ciudad FROM (SELECT TOP 1 ciudad, count(ciudad) as cuantas FROM Pagos
Where user_id = " & usuario &" group by ciudad order by cuantas desc) as mi_ciudad) where usuarios.User_id = " & usuario

Siento el ladrillo, y gracias por intentar entender esta "locura", mal parida desde el minuto 1.

Por cierto la base de datos en Sybase, pero también me vale lo que sea para mysql.