Check if a client appears more than once in a table

Question

sso I couldn't exactly express what I need in the title. I'm very new to mysql First of all, this is the sql table I'm currenly using http://dl.dropbox.com/u/37057843/P2_Scripts.sql

I'll explain briefly: I need to get the clients that were more than once in a (the) hotel and at least once in every type of room. (check the explanation of the tables below)

These are the tables:

• reservas (reservations), that contains the ID of the reservation (id), the ID of the client (idCliente) that reserved and the room number (idHabita).
• clientes, client table that contains his id, his name (nombre), his surname (apellido), his address and phone number (those last two aren't used)
• habitaciones (rooms) which contains the room number (id), the size in m2 (superficie), several things that don't matter and lastly the id of the price (idPrecio).
• precios (prices), which contains the id of the entry, the type of room (tipo) and the price (precio). The value of TIPO can be 'individual' or 'double'. like individual or double room.

So basically,

• reservas.idCliente = clientes.id
• reservas.idHabita = habitaciones.id
• habitaciones.precio = precio.id

And as I mentioned earlier, I need to get the clients that were at least once in every type of room, so basically once or more in individual room AND once or more in the double room.

I know this is kinda bad explained but I don't know how to explain it better, so sorry for that.

I'm using this query at the moment to get the people (names, surnames & room nr) that were in individual rooms

SELECT c.nombre, c.apellidos, r.idHabita
FROM  clientes c, reservas r, precios p, habitaciones h
WHERE r.idCliente = c.id
AND r.idHabita = h.id
AND h.idPrecio = p.id
AND p.tipo LIKE 'individual'

and I know that I have to do some type of subquery to check if p.tipo is 'double' as well, but I just don't get it working. The clients this query returns are the same ones that were in double rooms as well. In fact, these are the only ones that ever were in a individual room. But I need a query to check that they were in double rooms as well.

Thanks in advance for reading!

Answer 1

This shows rows for every room that qualifies (multiple rooms per person - at least one individual and at least one doble) - exactly the output that you requested (btw, accept the answers when they help you, or people will stop helping you):

SELECT c.nombre, c.apellidos, r.idHabita
FROM clientes c
INNER JOIN reservas r
ON r.idCliente = c.id
INNER JOIN habitaciones h
ON r.idHabita = h.id
WHERE r.idCliente IN
(SELECT r1.idCliente
FROM clientes c1
INNER JOIN reservas r1
ON r1.idCliente = c1.id
INNER JOIN habitaciones h1
ON r1.idHabita = h1.id
INNER JOIN precios p1
ON h1.idPrecio = p1.id
WHERE p1.tipo = 'individual')
AND r.idCliente IN
(SELECT r2.idCliente
FROM clientes c2
INNER JOIN reservas r2
ON r2.idCliente = c2.id
INNER JOIN habitaciones h2
ON r2.idHabita = h2.id
INNER JOIN precios p2
ON h2.idPrecio = p2.id
WHERE p2.tipo = 'doble')
ORDER BY c.nombre, c.apellidos, r.idHabita

UPDATE: just added sorting...

Answer 2

To get the clients "that were at least once in every type of room", join the clients table with their associated reservations and group the results by client—you then want those groups which have the same number of distinct room types as there are in the room types table, which can be expressed in a HAVING clause:

SELECT   c.*
FROM     clientes     c
  JOIN   reservas     r ON r.idCliente = c.id
  JOIN   habitaciones h ON r.idHabita  = h.id
  JOIN   precios      p ON h.idPrecio  = p.id
GROUP BY c.id
HAVING   COUNT(DISTINCT p.tipo) = (
  SELECT COUNT(DISTINCT   tipo) FROM precios
)

See it on sqlfiddle.