Hii! I have the following project:
DROP TABLE IF EXISTS PARTY, MUNICIPALITY, STANDFOR, POLLING_STATION, ELECTORAL_BOARD, CENSUS, COUNT CASCADE;
CREATE TABLE IF NOT EXISTS PARTY (
acronym VARCHAR(20) PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
address VARCHAR(50) NOT NULL);
CREATE TABLE IF NOT EXISTS MUNICIPALITY (
name VARCHAR(50) PRIMARY KEY,
locality VARCHAR(50) NOT NULL,
total_votes INTEGER);
CREATE TABLE IF NOT EXISTS STANDFOR (
acronym VARCHAR(20) REFERENCES PARTY(acronym) ON DELETE CASCADE ON UPDATE CASCADE,
name VARCHAR(50) REFERENCES MUNICIPALITY(name) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (acronym,name));
CREATE TABLE IF NOT EXISTS POLLING_STATION (
ndistrict INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL REFERENCES MUNICIPALITY(name) ON DELETE CASCADE ON UPDATE CASCADE,
presides VARCHAR(9) );
CREATE TABLE IF NOT EXISTS ELECTORAL_BOARD (
ndistrict INTEGER REFERENCES POLLING_STATION(ndistrict) ON DELETE CASCADE ON UPDATE CASCADE,
number INTEGER,
null_votes INTEGER,
blank_votes INTEGER,
presides VARCHAR(9),
PRIMARY KEY (ndistrict,number));
CREATE TABLE IF NOT EXISTS CENSUS (
idcard VARCHAR(9) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
votes_ndist INTEGER NOT NULL,
votes_num INTEGER NOT NULL,
member_ndist INTEGER,
member_num INTEGER,
FOREIGN KEY (votes_ndist,votes_num) REFERENCES ELECTORAL_BOARD(ndistrict,number),
FOREIGN KEY (member_ndist,member_num) REFERENCES ELECTORAL_BOARD(ndistrict,number) ON DELETE SET NULL ON UPDATE SET NULL);
ALTER TABLE POLLING_STATION
ADD FOREIGN KEY (presides) REFERENCES CENSUS(idcard) ON DELETE SET NULL ON UPDATE SET NULL;
ALTER TABLE ELECTORAL_BOARD
ADD FOREIGN KEY (presides) REFERENCES CENSUS(idcard) ON DELETE SET NULL ON UPDATE SET NULL;
CREATE TABLE IF NOT EXISTS COUNT (
ndistrict INTEGER,
number INTEGER,
FOREIGN KEY (ndistrict,number) REFERENCES ELECTORAL_BOARD(ndistrict,number) ON DELETE CASCADE ON UPDATE CASCADE,
acronym VARCHAR(20) REFERENCES PARTY(acronym) ON DELETE CASCADE ON UPDATE CASCADE,
nvotes INTEGER NOT NULL,
PRIMARY KEY(ndistrict,number,acronym));
I need a query that returns votes and acronyms of the parties whose number of votes in the municipality m1 is equal to the number of invalid votes plus the number of blank votes in that same municipality. I have thought of a meeting between POLLING STATION and ELECTORAL BOARD, and a correlated subquery with a grouping that would give the total of adding all the null and blank votes, but I'm a bit lost. Thanks for the help!