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