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

Navigation
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 look at all the categories and the counts 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

Now I need the column available to return only vehicles that are not or will be leased in a period > starting dt < dt_fim , but I could not.

Can anyone give me a light on where to go?

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

    
asked by anonymous 22.11.2016 / 14:28

2 answers

4

To return the available vehicles you use 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)

Soon after applying for 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)
    
22.11.2016 / 14:37
2

You can use the BETWEEN and EXISTS to check if there is any data in the location table that 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 over a period

For non-leased use not exists and for those that will only exist

    
22.11.2016 / 14:40
Why did the context change? Which .NET should I use?