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