Consultation with Inner Join

Question

Consultation with Inner Join

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)
#4 by (0 votes)

1

A few days ago I asked a question to get data from a database linking several tables with **INNER JOIN** .

Now I'm trying to mix the INNER and the COUNT but I do not know how to express it.

I have these tables.

Table: w_clients

Table: w_reservas

What I want to do is a query that takes from a range of dates the list of clients ordered by the number of classes they have reserved in that period.

The query I did a few days ago, did something similar. Show reservation name and customer name.

SELECT cl.nombre,c.nombre From wp_ac_reservas r 
INNER JOIN wp_ac_clases cl on r.id_clase =cl.id 
INNER JOIN wp_ac_clientes c on c.id = r.id_usuario 
WHERE r.fecha_reserva BETWEEN '2017-01-01' AND '2017-03-31'

But now I need only Client name, last name and number of reservations from x to x date (you would have to enter a count by means of I guess)

I already appreciate the interest.

mysql sql

asked by Jesús 03.05.2017 в 11:22

source

4 answers

Delete or catch html style in POI What is a char data? a char data can be a numerical value? [closed]

score 2 · Answer 1

You can use COUNT (*) and then group by id_cliente :

This shows the number of reservations of all customers in a range of dates

SELECT
  c.nom_cliente, COUNT(*) as total
FROM
  w_clientes c
INNER JOIN 
  w_reservas r
ON c.id_cliente=r.id_cliente
WHERE r.fecha_reserva BETWEEN '2017-01-01' AND '2017-12-31'
GROUP BY c.id_cliente;

Here is the number of reservations for a specific client in a range of dates

I would separate the two criteria from WHERE , to avoid surprises.

SELECT
  c.nom_cliente, COUNT(*) as total
FROM
  w_clientes c
INNER JOIN 
  w_reservas r
ON c.id_cliente=r.id_cliente
WHERE (r.fecha_reserva BETWEEN '2017-01-01' AND '2017-12-31') AND (c.id_cliente=1)
GROUP BY c.id_cliente;

COMPLETE EXAMPLE

In the example, only the reservations between the indicated dates are counted, ignoring those of the year 2018.

DEMO SQL Fiddle

MySQL 5.6 Schema Setup :

CREATE TABLE w_clientes
    ('id_cliente' int, 'nom_cliente' varchar(70))
;

INSERT INTO w_clientes
    ('id_cliente', 'nom_cliente')
VALUES
    (1, 'Pedro'),
    (2, 'Santiago'),
    (3, 'Juan')
;


CREATE TABLE w_reservas
    ('id_reserva' int, 'id_cliente' int, 'fecha_reserva' date )
;

INSERT INTO w_reservas
    ('id_reserva', 'id_cliente', 'fecha_reserva')
VALUES
    (1, 1, '2017-01-05'),
    (2, 1, '2017-02-05'),
    (3, 1, '2017-03-05'),
    (4, 2, '2017-04-05'),
    (5, 3, '2017-09-06'),
    (6, 1, '2017-08-05'),
    (7, 3, '2018-01-01'),
    (8, 2, '2018-01-02'),
    (9, 1, '2018-02-07')
;

Query 1 :

SELECT
  c.nom_cliente, COUNT(*) as total
FROM
  w_clientes c
INNER JOIN 
  w_reservas r
ON c.id_cliente=r.id_cliente
WHERE r.fecha_reserva BETWEEN '2017-01-01' AND '2017-12-31'
GROUP BY c.id_cliente

Results :

| nom_cliente | total |
|-------------|-------|
|       Pedro |     4 |
|    Santiago |     1 |
|        Juan |     1 |

score 0 · Answer 2

I leave a query made to the "flight", so there may be something that does not work well at all. Basically, what you have to do is group by the value that interests you and take the count. Something like this:

select w_clientes.id_usuario, w_clientes.nombre, count(w_clientes.id_usuario)
from w_clientes
inner join w_reservas on w_reservas.id_usuario = w_clientes.id_usuario
where w_reservas.fecha_reserva>='2017-01-01' and w_reservas.fecha_reserva<='2017-03-30'
group by w_clientes.id_usuario, w_clientes.nombre

I hope it serves you

score 0 · Answer 3

It is not clear what you want to do with the count, if you count how many records there are, how many reservations a client has made or you have simply misinterpreted what I think.

So you can get a list of the records with name and surname and id_reserva I have ignored the count because of your question I interpret that really is not what you need.

SELECT 
/* decimos que columnas queremos */
   c.nombre,
   c.apellido,
   r.id id_reserva
FROM 
/* indicamos la tabla principal de la consulta */
    w_clientes c
INNER JOIN 
/* unimos otra tabla por su relación  con la principal */
    w_reservas r ON c.id_usuario = r.id_usuario 
WHERE 
/* indicamos las condiciones que deben cumplir los registros */
    r.fecha_reserva BETWEEN '2017-01-01' AND '2017-03-31'
/* ordenamos los registros en base a la columna o columnas que indiquemos */
ORDER BY r.id_clase ASC

score 0 · Answer 4

All the answers you have been given are valid, but according to the criteria can be ambiguous. When the need is I want to know the amount of reservations that each client has? and we have clients who do not have reservations. How do we show them? We show them ?. The examples that have happened to you do not show them, so, to complete a bit, I will attach an example where 0 will be shown in the amount of reservations when the client does not have. This criterion is more specific and clear for some users, but it can clearly make extremely long reports.

select  c.nombre,
    c.apellidos,
    ISNULL(count(r.id_reserva),0) AS 'Cantidad'
    from w_clientes c
    left join w_reservas r
        on r.id_cliente = c.id_cliente
        and r.fecha_reserva BETWEEN '2017-01-01' AND '2017-03-31'
    group by 
        c.nombre,
        c.apellidos