Create a SELECT to bring the difference of two tables

2

You have the bisemanas table that stores 26 fixed bi-weeks and the outdoor_bisemanas table that stores the billboards reserves according to the selected bi-week.

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

This% of% I made is bringing the difference between the two weeks and each bi-week registered in SELECT 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)
)
    
asked by anonymous 24.08.2017 / 20:49

4 answers

1

To bring in all the weekends that are not bound to any billboards, the 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;
    
24.08.2017 / 21:01
1

Hello, try this:

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

Hugs,

    
24.08.2017 / 21:03
1

For this query, you need to select all records from the bisemanas table, make a LEFT JOIN with the outdoor_bisemanas table, and filter only those records where the primary key of the outdoor_bisemanas table returned NULL , as it indicates that JOIN did not match 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: link

    
24.08.2017 / 21:02
0

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
    
29.08.2017 / 18:02