Doubt using RIGHT JOIN on Sql Server

0

How could I return the results of these three tables, even if I did not have a relationship between them. 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
    
asked by anonymous 01.10.2015 / 04:49

2 answers

0

The answer looks like this:

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
    
01.10.2015 / 05:31
0

Use CROSS JOIN if you do not want to search for a relationship between them.

    
07.10.2015 / 12:01