union of 3 tables in one

Question

union of 3 tables in one

Navigation

#1 by (2 votes)

1

Hello world programmer ....... I have a doubt I hope you can help me what happens is that I have a table in sql with the following fields:

LINEA  varchar(10) primary key
GRUPO varchar(10) primary key
SUB_GRUPO varchar(10) primary key
,DESCRIPCION LARGA  varchar(50)
DESCRIPCION CORTA  varchar(10)

What I try to do in this table is to join 3 tables in one, I explain: the line field belonged to a table called LINEA with its long and short description, the field group belonged to a table called GROUP with its long and short description, and so for the sub_group

What I want to do is the following:

that when registering a new Line is stored in the field line with its long and short description, the group and sub group fields must be empty,

LINEAS | GRUPOS | SUB_GRUPOS | DESCRIP_LARGA   |  DESCRIP_CORTA
tableros |      |            | tableros de plasticos | tabplas

and when registering a GROUP the existing LINES must appear to be able to create the group, consequently the field sub_group must be empty,

LINEAS   | GRUPOS     | SUB_GRUPOS | DESCRIP_LARGA   |  DESCRIP_CORTA
tableros | empotrados |            | empotrados de 2      | emp2

and finally when you want to add a sub_group 1 I have to know the line then the group and there create the new sub_grupo I do not know if I let myself understand,

LINEAS   | GRUPOS    | SUB_GRUPOS   | DESCRIP_LARGA   |  DESCRIP_CORTA
tableros |empotrados  | tapa blanca | tapas de 4      | tab4

That's the way I want to save my data in a single table I hope you can help me or give me an alternative solution.

sql-server

asked by Michael Bj 24.08.2017 в 23:51

source

1 answer

Perform cycle while from a MySQLi query in PHP Create Button to return to the Android studio application

score 2 · Answer 1

As a first point you should be clear that you are missing the rules of normalization and conceptual design of databases. That said, if you want to insert null values in the Groups and Subgroups columns, you must remove the Primary Key or Primary Key option from the creation of your table. As a general rule, a password will not accept null values.

To guarantee the integrity of your data, the design you should have is the following:

I leave you the script with which the tables that I show in the image were created:

CREATE TABLE dbo.Lineas
    (
    LineaID varchar(50) NOT NULL,
    Linea varchar(50) NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Lineas ADD CONSTRAINT
    PK_Lineas PRIMARY KEY CLUSTERED 
    (
    LineaID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Lineas SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Grupos
    (
    GrupoID int NOT NULL,
    LineaID varchar(50) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Grupos ADD CONSTRAINT
    PK_Grupos PRIMARY KEY CLUSTERED 
    (
    GrupoID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Grupos ADD CONSTRAINT
    FK_Grupos_Lineas FOREIGN KEY
    (
    LineaID
    ) REFERENCES dbo.Lineas
    (
    LineaID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
ALTER TABLE dbo.Grupos SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.SubGrupos
    (
    SubGrupoID int NOT NULL,
    GrupoID int NOT NULL,
    SubGrupo varchar(50) NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.SubGrupos ADD CONSTRAINT
    PK_SubGrupos PRIMARY KEY CLUSTERED 
    (
    SubGrupoID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.SubGrupos ADD CONSTRAINT
    FK_SubGrupos_Grupos FOREIGN KEY
    (
    GrupoID
    ) REFERENCES dbo.Grupos
    (
    GrupoID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION 

GO
ALTER TABLE dbo.SubGrupos SET (LOCK_ESCALATION = TABLE)
GO
COMMIT