Relationship between 4 tables

Question

Relationship between 4 tables

Navigation

#1 by (2 votes)

0

I have the tables:

-- NORTE --
create table tb_indices_norte(
id_norte int not null auto_increment,
localidade varchar(30),
sem_epi int not null,
totOvos smallint,
num_pos smallint,
num_inst smallint, 
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_norte)) default charset = utf8; 

-- SUL --
create table tb_indices_sul(
id_sul int not null auto_increment,
localidade varchar(30),
sem_epi int not null,
totOvos smallint,
num_pos smallint,
num_inst smallint, 
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_sul)) default charset = utf8;

-- LESTE --
create table tb_indices_leste(
id_leste int not null auto_increment,
localidade varchar(30),
sem_epi int not null,
totOvos smallint,
num_pos smallint, 
num_inst smallint,
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_leste)) default charset = utf8;

-- OESTE--
create table tb_indices_oeste(
id_oeste int not null auto_increment,
localidade varchar(30),
sem_epi int not null,
totOvos smallint,
num_pos smallint, 
num_inst smallint,
ipo decimal(5,1),
ido decimal(5,1),
ano varchar(4),
primary key(id_oeste)) default charset = utf8;

How do I make a SELECT that returns me to SUM (totOvos), SUM (num_pos), SUM (num_inst) between the 4 tables WHERE year = 'X' AND sem_epi BETWEEN 'Y' AND 'Z' GROUP BY sem_epi

I'm a beginner in SQL, I know I have to define the PRIMARY KEYS and the FOREIGN KEY, my problem is just right, how to define in the tables and how to create this SELECT.

mysql sql

asked by anonymous 05.02.2018 / 22:45

1 answer

How do I pass this string in number to calculate in another array? Continuous bounce jquery effect

score 2 · Answer 1

Amiraldo observing its schema, as Motta commented, it is not necessary to create 4 tables, just create 2 where one stores the data NORTH, SOUTH, EAST and EAST (each with its respective ID) and the other stores the details day, week, month, year, required as you requested. I made a scene here where you can adopt it or modify it to your liking.

Below is the Code:

Indices table

CREATE TABLE Indices(
    ID INT IDENTITY PRIMARY KEY NOT NULL,
    Indice NVARCHAR(15) UNIQUE NOT NULL
)
GO

Details Table

CREATE TABLE DetalhesDoDia(
    ID INT IDENTITY PRIMARY KEY NOT NULL,
    IndicesID INT NOT NULL,
    Localidade NVARCHAR(30) NOT NULL,
    SemEPI SMALLINT NOT NULL DEFAULT 0,
    Ovos SMALLINT,
    NumPos SMALLINT,
    NumInst SMALLINT,
    IPO DECIMAL(5,1),
    IDO DECIMAL(5,1),
    Ano NVARCHAR(4),

    CONSTRAINT FK_IndicesDoDetalhe
    FOREIGN KEY (IndicesID)
    REFERENCES [dbo].[Indices](ID)
)
GO

Select which returns all data

SELECT Indices.Indice, Detalhes.Localidade, Detalhes.SemEPI, Detalhes.Ovos, Detalhes.NumPos, Detalhes.NumInst, Detalhes.IPO, Detalhes.IDO, Detalhes.Ano
FROM [dbo].[DetalhesDoDia] AS Detalhes
INNER JOIN [dbo].[Indices] AS Indices
ON Detalhes.[IndicesID] = Indices.[ID]
GO

Select with Sum

SELECT SUM(Detalhes.Ovos) AS 'Total OVOS', SUM(Detalhes.NumPos) AS 'Total POS', SUM(Detalhes.NumInst) AS 'Total INST'
FROM [dbo].[DetalhesDoDia] AS Detalhes
INNER JOIN [dbo].[Indices] AS Indices
ON Detalhes.[IndicesID] = Indices.[ID]
WHERE Detalhes.Ano = '2018'
AND Detalhes.SemEPI BETWEEN 0 AND 5
GROUP BY Detalhes.SemEPI