Doubt using RIGHT JOIN in Sql Server

Asked

Viewed 62 times

0

How could I return the results of these three tables, even if I did not have a relationship between them. I thank you

SELECT A.IDPLANOCONTAS, A.NUMERO_CONTA AS NUMERO_CONTA1, A.DESCRICAO_CONTA AS DESCRICAO_CONTA1 , A.OPERACAO AS OPERACAO1,
       B.IDPLANOCONTAS_NIVEL2, B.NUMERO_CONTA AS NUMERO_CONTA2, B.DESCRICAO_CONTA AS DESCRICAO_CONTA2, 
       C.IDPLANOCONTAS_NIVEL3, C.NUMERO_CONTA AS NUMERO_CONTA3, C.DESCRICAO_CONTA AS DESCRICAO_CONTA3 
 FROM  TB_PLANO_CONTAS A 
RIGHT JOIN  TB_PLANO_CONTAS_NIVEL2 B ON A.IDPLANOCONTAS = B.IDPLANOCONTAS 
RIGHT JOIN  TB_PLANO_CONTAS_NIVEL3 C ON B.IDPLANOCONTAS_NIVEL2 = C.IDPLANOCONTAS_NIVEL2

This is the script of the three tables with sample data.

USE [BANCO_TESTE]
GO
/****** Object:  Table [dbo].[TB_PLANO_CONTAS]    Script Date: 09/30/2015 23:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_PLANO_CONTAS](
    [IDPLANOCONTAS] [int] IDENTITY(1,1) NOT NULL,
    [NUMERO_CONTA] [char](10) NULL,
    [DESCRICAO_CONTA] [varchar](50) NULL,
    [OPERACAO] [char](1) NULL,
    [IDUSUARIO] [int] NOT NULL,
    [STATUS] [char](1) NULL,
    [MARCA] [char](1) NULL,
    [DATA_INC] [datetime] NULL,
    [DATA_HAB] [datetime] NULL,
    [DATA_ALT] [datetime] NULL,
    [IDEMPRESA] [int] NOT NULL,
 CONSTRAINT [PK__TB_PLANO__5EFC6941108B795B] PRIMARY KEY CLUSTERED 
(
    [IDPLANOCONTAS] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ__TB_PLANO__5EFC69401367E606] UNIQUE NONCLUSTERED 
(
    [IDPLANOCONTAS] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[TB_PLANO_CONTAS] ON
INSERT [dbo].[TB_PLANO_CONTAS] ([IDPLANOCONTAS], [NUMERO_CONTA], [DESCRICAO_CONTA], [OPERACAO], [IDUSUARIO], [STATUS], [MARCA], [DATA_INC], [DATA_HAB], [DATA_ALT], [IDEMPRESA]) VALUES (1, N'125.255   ', N'DESCRIÇÃO', N'D', 3, N'N', N'N', CAST(0x0000A52100000000 AS DateTime), NULL, NULL, 1)
SET IDENTITY_INSERT [dbo].[TB_PLANO_CONTAS] OFF
/****** Object:  Table [dbo].[TB_PLANO_CONTAS_NIVEL2]    Script Date: 09/30/2015 23:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_PLANO_CONTAS_NIVEL2](
    [IDPLANOCONTAS_NIVEL2] [int] IDENTITY(1,1) NOT NULL,
    [IDPLANOCONTAS] [int] NOT NULL,
    [NUMERO_CONTA] [char](10) NULL,
    [DESCRICAO_CONTA] [varchar](50) NULL,
    [STATUS] [char](1) NULL,
    [MARCA] [char](1) NULL,
    [DATA_INC] [datetime] NULL,
    [DATA_HAB] [datetime] NULL,
    [DATA_ALT] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [IDPLANOCONTAS_NIVEL2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
    [IDPLANOCONTAS_NIVEL2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[TB_PLANO_CONTAS_NIVEL2] ON
INSERT [dbo].[TB_PLANO_CONTAS_NIVEL2] ([IDPLANOCONTAS_NIVEL2], [IDPLANOCONTAS], [NUMERO_CONTA], [DESCRICAO_CONTA], [STATUS], [MARCA], [DATA_INC], [DATA_HAB], [DATA_ALT]) VALUES (2, 1, N'125.255   ', N'DESCRIÇÃO 02', N'N', N'N', NULL, NULL, NULL)
INSERT [dbo].[TB_PLANO_CONTAS_NIVEL2] ([IDPLANOCONTAS_NIVEL2], [IDPLANOCONTAS], [NUMERO_CONTA], [DESCRICAO_CONTA], [STATUS], [MARCA], [DATA_INC], [DATA_HAB], [DATA_ALT]) VALUES (3, 1, N'125.256   ', N'DESCRIÇÃO 02', N'N', N'N', NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[TB_PLANO_CONTAS_NIVEL2] OFF
/****** Object:  Table [dbo].[TB_PLANO_CONTAS_NIVEL3]    Script Date: 09/30/2015 23:40:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TB_PLANO_CONTAS_NIVEL3](
    [IDPLANOCONTAS_NIVEL3] [int] IDENTITY(1,1) NOT NULL,
    [IDPLANOCONTAS_NIVEL2] [int] NOT NULL,
    [NUMERO_CONTA] [char](10) NULL,
    [DESCRICAO_CONTA] [varchar](50) NULL,
    [STATUS] [char](1) NULL,
    [MARCA] [char](1) NULL,
    [DATA_INC] [datetime] NULL,
    [DATA_HAB] [datetime] NULL,
    [DATA_ALT] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [IDPLANOCONTAS_NIVEL3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
    [IDPLANOCONTAS_NIVEL3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[TB_PLANO_CONTAS_NIVEL3] ON
INSERT [dbo].[TB_PLANO_CONTAS_NIVEL3] ([IDPLANOCONTAS_NIVEL3], [IDPLANOCONTAS_NIVEL2], [NUMERO_CONTA], [DESCRICAO_CONTA], [STATUS], [MARCA], [DATA_INC], [DATA_HAB], [DATA_ALT]) VALUES (2, 2, N'125.258   ', N'DESCRIÇÃO 03', N'N', N'N', NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[TB_PLANO_CONTAS_NIVEL3] OFF
/****** Object:  Default [DF__TB_PLANO___STATU__15502E78]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS] ADD  CONSTRAINT [DF__TB_PLANO___STATU__15502E78]  DEFAULT ('N') FOR [STATUS]
GO
/****** Object:  Default [DF__TB_PLANO___MARCA__164452B1]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS] ADD  CONSTRAINT [DF__TB_PLANO___MARCA__164452B1]  DEFAULT ('N') FOR [MARCA]
GO
/****** Object:  Default [DF__TB_PLANO___STATU__0697FACD]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS_NIVEL2] ADD  DEFAULT ('N') FOR [STATUS]
GO
/****** Object:  Default [DF__TB_PLANO___MARCA__078C1F06]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS_NIVEL2] ADD  DEFAULT ('N') FOR [MARCA]
GO
/****** Object:  Default [DF__TB_PLANO___STATU__0F2D40CE]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS_NIVEL3] ADD  DEFAULT ('N') FOR [STATUS]
GO
/****** Object:  Default [DF__TB_PLANO___MARCA__10216507]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS_NIVEL3] ADD  DEFAULT ('N') FOR [MARCA]
GO
/****** Object:  ForeignKey [FK__TB_PLANO___IDEMP__151B244E]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS]  WITH CHECK ADD  CONSTRAINT [FK__TB_PLANO___IDEMP__151B244E] FOREIGN KEY([IDEMPRESA])
REFERENCES [dbo].[TB_EMPRESA] ([IDEMPRESA])
GO
ALTER TABLE [dbo].[TB_PLANO_CONTAS] CHECK CONSTRAINT [FK__TB_PLANO___IDEMP__151B244E]
GO
/****** Object:  ForeignKey [FK__TB_PLANO___IDUSU__25518C17]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS]  WITH CHECK ADD  CONSTRAINT [FK__TB_PLANO___IDUSU__25518C17] FOREIGN KEY([IDUSUARIO])
REFERENCES [dbo].[TB_USUARIO] ([IDUSUARIO])
GO
ALTER TABLE [dbo].[TB_PLANO_CONTAS] CHECK CONSTRAINT [FK__TB_PLANO___IDUSU__25518C17]
GO
/****** Object:  ForeignKey [FK__TB_PLANO___IDPLA__11158940]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS_NIVEL2]  WITH CHECK ADD FOREIGN KEY([IDPLANOCONTAS])
REFERENCES [dbo].[TB_PLANO_CONTAS] ([IDPLANOCONTAS])
GO
/****** Object:  ForeignKey [FK__TB_PLANO___IDPLA__1209AD79]    Script Date: 09/30/2015 23:40:00 ******/
ALTER TABLE [dbo].[TB_PLANO_CONTAS_NIVEL3]  WITH CHECK ADD FOREIGN KEY([IDPLANOCONTAS_NIVEL2])
REFERENCES [dbo].[TB_PLANO_CONTAS_NIVEL2] ([IDPLANOCONTAS_NIVEL2])
GO
sql sql-server

  • There is already an explanation for this, following link: 
 http://answall.com/questions/6441/qual-%C3%A9-a-difference%C3%A7a-entre-Inner-Join-e-outer-Join

  • Very grateful for your help!;

  • There is nowhere to go! The people here help me and I pass on what I learned ^^

2 answers

0

The answer was thus:

SELECT A.IDPLANOCONTAS, A.NUMERO_CONTA AS NUMERO_CONTA1, A.DESCRICAO_CONTA AS DESCRICAO_CONTA1 , A.OPERACAO AS OPERACAO1,
       b.IDPLANOCONTAS, B.IDPLANOCONTAS_NIVEL2, B.NUMERO_CONTA AS NUMERO_CONTA2, B.DESCRICAO_CONTA AS DESCRICAO_CONTA2, 
       C.IDPLANOCONTAS_NIVEL2, C.IDPLANOCONTAS_NIVEL3, C.NUMERO_CONTA AS NUMERO_CONTA3, C.DESCRICAO_CONTA AS DESCRICAO_CONTA3 
 FROM  TB_PLANO_CONTAS A 
left join  TB_PLANO_CONTAS_NIVEL2 B ON A.IDPLANOCONTAS = B.IDPLANOCONTAS 
left join  TB_PLANO_CONTAS_NIVEL3 C ON B.IDPLANOCONTAS_NIVEL2 = C.IDPLANOCONTAS_NIVEL2

0

Use CROSS JOIN if you don’t want to search for a relationship between them.

  • 1

    Hi Alberto, welcome to [en.so]. Could you explain how the use of CROSS JOIN would look in the context of the question? And why did he suggest it? That is, what are its advantages that deserve the temptation to use it?

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.