Create a SELECT to bring the difference of two tables

Question

Has the table bisemanas which stores 26 fixed bi-weeks and the table outdoor_bisemanas which stores the reserves of billboards according to the bi-week chosen.

Edit: What I want is a select which displays all bi-weeks of the table bisemanas that are not registered in the table outdoor_bisemanas of each billboard. That is when I select a billboard, I want SELECT to bring all bisemanas that are not registered in the table outdoor_bisemanas with the id of the selected billboard.

This SELECT that I did this bringing the difference between the bi-week and each bi-week registered in outdoor_bisemanas separately.

SELECT b.id_bisemanas
      ,b.data_inicio
      ,b.data_fim
      ,o.id_outdoor
  FROM bisemanas AS b
 INNER JOIN outdoor_bisemanas AS ob
    ON b.id_bisemanas != ob.id_bisemanas
 INNER JOIN outdoor AS o
    ON o.id_outdoor = ob.id_outdoor
 WHERE b.ano = '2017' && b.data_inicio BETWEEN CURRENT_DATE() 
   AND '2017-12-31' &&  ob.id_outdoor = '1'
 GROUP BY b.id_bisemanas

---

Create Table:

CREATE TABLE bisemanas (
id_bisemanas INT(11) AUTO_INCREMENT PRIMARY KEY,
ano INT(4) NOT NULL,
nome_bi VARCHAR(25) NOT NULL,
data_inicio DATE,
data_fim DATE
)

CREATE TABLE outdoor_bisemanas (
id_bisemanas INT(11) PRIMARY KEY,
id_outdoor INT(11) NOT NULL,
id_usuario INT(11) NOT NULL,
valor_total float,
FOREIGN KEY (id_bisemanas) REFERENCES bisemanas(id_bisemanas)
)

Answer 1

To bring all bisemanas that are not linked to any billboard, select below does this:

SELECT *
  FROM bisemanas bs
 WHERE NOT EXISTS (SELECT 1
          FROM outdoor_bisemanas obs
         WHERE bs.id_bisemanas = obs.id_bisemanas);

or

SELECT *
  FROM bisemanas bs
  LEFT JOIN outdoor_bisemanas obs
    ON bs.id_bisemanas = obs.id_bisemanas
 WHERE obs.id_bisemanas IS NULL;

Answer 2

For this query, you need to select all table records bisemanas, make a LEFT JOIN with the table outdoor_bisemanas and filter only by records where the table primary key outdoor_bisemanas returned NULL, indicates that the JOIN did not marry any line. Example:

SELECT b.*
  FROM bisemanas AS b
  LEFT JOIN outdoor_bisemanas AS ob
    ON b.id_bisemanas = ob.id_bisemanas
 WHERE (ob.id_bisemanas IS NULL);

Fiddle with the example: https://www.db-fiddle.com/f/jdJBAv51ssSd9WAFWAjHDd/0

Answer 3

Hello, try this on:

SELECT [ campos ]
  FROM bisemanas b
  LEFT JOIN outdoor_bisemanas o
    ON b.id_bisemanas = o.id_bisemanas
 WHERE o.id_bisemanas IS NULL

Hug,

Answer 4

The answer I was looking for was this:

SELECT o.id_endereco,CONCAT(o.id_outdoor,'-',b.id_bisemanas) as chave FROM outdoor as o,bisemanas as b WHERE CONCAT(o.id_outdoor,'-',b.id_bisemanas) NOT IN(SELECT CONCAT(ob.id_outdoor,'-',ob.id_bisemanas) as chave FROM outdoor_bisemanas as ob) ORDER BY `chave` ASC