count of 2 tables in mysql

Question

count of 2 tables in mysql

Navigation

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

0

I have 3 tables in mysql, one is customer, another store and reserve, this is related to the customer table and steals the id and the store tbl and also steals your id, my question is, how can I make a list of all the clients and know how many reservations do I have ?, since I may have many or none, and try with a count and only take into account the ones that have been booked, and I want everyone to take me into account, and that they did not do any in any case give me count 0, how could I control that?

customer table

customer_id name

phone

city

address

store table

id_tienda

nro

cost

dimension

reservation table

reservation_id

start_date

final_date

condition

customer_id

id_tienda

I've tried this query:

SELECT c.Id_cliente, c.Nombre, COUNT(r.Id_reserva) as total
FROM cliente c 
LEFT JOIN reserva r 
ON c.Id_cliente = r.Id_cliente
GROUP BY c.Id_cliente

It works for me however, I forgot a detail, in the reservation table, I have a condition field that can be 1 or 0, it is 1 when they are valid and 0 when they end, when doing a where so that only I take in account the current reserves no longer works my query.

mysql sql

asked by Fernando Banegas 08.07.2017 в 00:51

source

4 answers

How to create a self-build DateTime field for 30 Minutes SQL Server? Android Calendar

score 1 · Answer 1

The simplest way is the following:

SELECT c.id_cliente, 
       c.nombre, 
       ISNULL(COUNT(1),0) AS cantidadReservas
       FROM cliente c 
       LEFT JOIN reserva r
           ON c.id_cliente = r.id_cliente
           AND r.condicion = 1
       GROUP BY
           c.id_cliente, 
           c.nombre

But using a less elegant and less optimal subquery:

SELECT id_cliente, 
           nombre, 
           (SELECT ISNULL(COUNT(1),0) 
                   FROM reserva r
                   WHERE c.id_cliente = r.id_cliente
                         AND r.condicion = 1
           ) AS cantidadReservas
           FROM cliente c

score 1 · Answer 2

You only have to implement a meeting of the two tables, defining that you always keep the total of records of one of them

Using LEFT JOIN

All the records in the table on the left and only the matching records in the right table.

SELECT id_cliente, nombre, COUNT(*) AS cantidadReservas
FROM cliente  
LEFT JOIN reserva USING(id_cliente)
GROUP BY id_cliente

Using RIGHT JOIN

All the records in the table on the right and only the records that match the left table.

SELECT id_cliente, nombre, COUNT(*) AS cantidadReservas
FROM reserva  
RIGHT JOIN cliente USING(id_cliente)
GROUP BY id_cliente

Keep in mind that I used USING(campo_en_comun) since the tables that come together have a field that is called equal and is exactly your comparison principle so you could have used the condition cliente.id_cliente=reserva.id_cliente , but in particular cases like These the first one is much more practical.

score 0 · Answer 3

try left join and join righ when you just do a join this returns the fields that are related if you use a left join as its name implies bring me the fields of my left table no matter if I ever had a field in common in this case would be the reserves

score 0 · Answer 4

This should be the instruction when you apply the where condition to obtain the current reservations (condition = 1), see example

SELECT c.Id_cliente, c.Nombre, COUNT(r.Id_reserva) as total 
  FROM cliente c LEFT JOIN reserva r 
    ON c.Id_cliente = r.Id_cliente 
    where r.condicion = 1
    GROUP BY c.Id_cliente 
union
   (select id_cliente, nombre, 0
       from cliente where id_cliente
             not in ( select id_cliente from reserva
                             where condicion = 1  ) );

The second select obtains the clients without the current reservations.