Create a SELECT to bring the difference of two tables

Question

Create a SELECT to bring the difference of two tables

Navigation

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

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)
)

php sql

asked by anonymous 24.08.2017 / 20:49

4 answers

Laravel 5.3 update field JSON MySql - # 3143 - Invalid JSON path expression How to get the name of the site?

score 1 · Answer 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;

score 1 · Answer 2

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

score 1 · Answer 3

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

score 0 · 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