how to query data in a table that is not related to the other table?

Asked

Viewed 142 times

3

I have the following tables:

CREATE TABLE IF NOT EXISTS `categoria` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(25) NOT NULL,
  `descricao` VARCHAR(100) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `veiculo` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `cat_id` INT NOT NULL,
  `modelo` VARCHAR(100) NULL,
  `placa` VARCHAR(7) NULL,
  PRIMARY KEY (`id`),
  INDEX `cat_id` (`cat_id` ASC),
  CONSTRAINT `cat_id`
    FOREIGN KEY (`cat_id`)
    REFERENCES `categoria` (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `locacao` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `veiculo_id` INT NOT NULL,
  `dt_inicio` DATETIME NOT NULL,
  `dt_fim` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `veiculo_id` (`veiculo_id` ASC),
  CONSTRAINT `veiculo_id`
    FOREIGN KEY (`veiculo_id`)
    REFERENCES `veiculo` (`id`))
ENGINE = InnoDB;

I would like to consult all categories and the count of each vehicle in each .. hence I built this query:

SELECT c.nome, count(c.id) AS disponiveis
FROM categoria AS c
INNER JOIN veiculo AS v
ON v.cat_id = c.id
GROUP BY c.id;

that returns me this:

nome, disponiveis
A - ECONÔMICO, 5
B - ECONÔMICO COM AR, 4
C - SEDAN COM AR, 2

Hence I need now that column available return me only vehicles that are not or will be leased in a period > dt_start and < dt_end, but I could not.

Someone can give me a light of where to go?

I tried to this but it did not go as expected. It continued returning all vehicles, until those that will be leased in a period X.

mysql sql database join

2 answers

4


To return the available vehicles you use the query:

SELECT v.*
  FROM veiculo v
 WHERE NOT EXISTS(SELECT l.id
                    FROM locacao l
                   WHERE l.veiculo_id = v.veiculo_id
                     AND '2016-11-22' BETWEEN l.dt_inicio AND l.dt_fim)

Logo applying to category we have:

SELECT c.nome,
       (SELECT count(v.id)
          FROM veiculo AS v
         WHERE v.cat_id = c.id
           AND NOT EXISTS(SELECT l.id
                            FROM locacao AS l
                           WHERE l.veiculo_id = v.veiculo_id
                             AND '2016-11-22' BETWEEN l.dt_inicio AND l.dt_fim)) AS disponiveis
  FROM categoria AS c

Or

SELECT c.nome,
       count(c.id) AS disponiveis
  FROM categoria AS c
       LEFT JOIN veiculo AS v ON v.cat_id = c.id
 WHERE NOT EXISTS(SELECT l.id
                    FROM locacao l
                   WHERE l.veiculo_id = v.veiculo_id
                     AND '2016-11-22' BETWEEN l.dt_inicio AND l.dt_fim)
  • 1

    It even solved my problem of researching vehicles in a category that are not leased. I only added in the WHERE of the first query the AND cat_id = 1 LIMIT 1. Thanks.

2

You can use the clauses BETWEEN and EXISTS to check if in the leasing table there are data with what you need.

SELECT c.nome, count(c.id) AS disponiveis
    FROM categoria AS c
    INNER JOIN veiculo AS v
    ON v.cat_id = c.id
    where exists (select 1 from locacao l
    where suadata BETWEEN l.dt_inicio and l.dt_fim
    and l.veiculo_id = v.id)
    GROUP BY c.id;

Detail; vehicles that are not or will be leased in a period

For the unsettled use not exists and for those who are only exists

Browser other questions tagged mysql sql database join

You are not signed in. Login or sign up in order to post.