August 24, 2009 at 12:36 pm
Hi!
I've always used non-ANSI joins in my queries. Now, in SQL Server 2008 Express Edition, it's not possible anymore (I know that's a compatibility mode but it's time to learn ANSI joins).
I have a simple query:
SELECT A.NOME,
A.MATRICULA,
CASE P.TIPO_AULA
WHEN 1 THEN 'REPOSIÇÃO/ADIANTAMENTO'
WHEN 2 THEN 'INFORMÁTICA'
WHEN 3 THEN 'DIGITAÇÃO'
WHEN 4 THEN 'AULA GRÁTIS'
END AS TIPAO,
P.DATA_HORA_ENTRADA,
P.DATA_HORA_SAIDA,
P.TEMPO,
S.CURSO
FROM PRESENCA P, ALUNOS A, SIMULADORES S, HORARIO H
WHERE P.COD_ALUNO = A.CODIGO
AND P.CURSO_ATUAL *= S.CODIGO --NEED HELP HERE
AND H.COD_ALUNO *= A.CODIGO --NEED HELP HERE
AND A.CODIGO <> 0
AND H.SALA <> 0
ORDER BY A.NOME
I need to convert it to non-ANSI join. I did:
SELECT A.NOME,
A.MATRICULA,
CASE P.TIPO_AULA
WHEN 1 THEN 'REPOSIÇÃO/ADIANTAMENTO'
WHEN 2 THEN 'INFORMÁTICA'
WHEN 3 THEN 'DIGITAÇÃO'
WHEN 4 THEN 'AULA GRÁTIS'
END AS TIPAO,
P.DATA_HORA_ENTRADA,
P.DATA_HORA_SAIDA,
P.TEMPO,
S.CURSO
FROM PRESENCA P
INNER JOIN ALUNOS A ON P.COD_ALUNO = A.CODIGO
LEFT OUTER JOIN SIMULADORES S ON P.CURSO_ATUAL = S.CODIGO --NEED HELP HERE
LEFT OUTER JOIN HORARIO H ON H.COD_ALUNO = A.CODIGO --NEED HELP HERE
WHERE A.CODIGO <> 0
AND H.SALA <> 0
ORDER BY A.NOME
But I having problem in the line:
LEFT OUTER JOIN HORARIO H ON H.COD_ALUNO = A.CODIGO
When can I do the all query in ANSI-join? I already read a lot of material but I don't understand this situation.
Thanks folks!
August 24, 2009 at 12:39 pm
Not sure what yhe problem is. What would help is the DDL for the tables (CREATE TABLE statements), sample data for the tables, and expected results based on the sample data.
For help meeting this request, please read and follow the instructions in the first article in my signature block about asking for help.
August 24, 2009 at 12:56 pm
OK, let's go!
Table Alunos
/****** Object: Table [dbo].[ALUNOS] Script Date: 08/24/2009 15:46:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ALUNOS](
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[MATRICULA] [int] NOT NULL,
[NOME] [nvarchar](50) NOT NULL,
[LOCAL_NASC] [int] NOT NULL,
[DATA_NASC] [date] NOT NULL,
[NOME_PAI] [nvarchar](50) NULL,
[NOME_MAE] [nvarchar](50) NULL,
[CPF] [nvarchar](20) NULL,
[RG] [nvarchar](20) NULL,
[ENDERECO] [nvarchar](100) NOT NULL,
[BAIRRO] [nvarchar](70) NOT NULL,
[CEP] [nchar](9) NOT NULL,
[CIDADE] [int] NOT NULL,
[FONE] [nvarchar](20) NULL,
[RESPONSAVEL] [nvarchar](50) NOT NULL,
[CPF_RESPONSAVEL] [nvarchar](20) NOT NULL,
[RG_RESPONSAVEL] [nvarchar](20) NOT NULL,
[ENDERECO_RESPONSAVEL] [nvarchar](100) NOT NULL,
[BAIRRO_RESPONSAVEL] [nvarchar](70) NOT NULL,
[CEP_RESPONSAVEL] [nchar](9) NOT NULL,
[CIDADE_RESPONSAVEL] [int] NOT NULL,
[FONE_RESPONSAVEL] [nvarchar](20) NULL,
[USUARIO] [int] NOT NULL,
[DATA_CADASTRO] [date] NOT NULL,
[CANCELADO] [nchar](1) NOT NULL,
[VENDEDOR] [int] NOT NULL,
[SENHA] [nvarchar](15) NOT NULL,
[DATA_INICIO_CURSO] [date] NOT NULL,
CONSTRAINT [PK_ALUNOS] PRIMARY KEY CLUSTERED
(
[CODIGO] 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
ALTER TABLE [dbo].[ALUNOS] WITH CHECK ADD CONSTRAINT [FK_ALUNOS_CIDADES] FOREIGN KEY([CIDADE])
REFERENCES [dbo].[CIDADES] ([CODIGO])
GO
ALTER TABLE [dbo].[ALUNOS] CHECK CONSTRAINT [FK_ALUNOS_CIDADES]
GO
ALTER TABLE [dbo].[ALUNOS] WITH CHECK ADD CONSTRAINT [FK_ALUNOS_CIDADES_RESPONSAVEL] FOREIGN KEY([CIDADE_RESPONSAVEL])
REFERENCES [dbo].[CIDADES] ([CODIGO])
GO
ALTER TABLE [dbo].[ALUNOS] CHECK CONSTRAINT [FK_ALUNOS_CIDADES_RESPONSAVEL]
GO
ALTER TABLE [dbo].[ALUNOS] WITH CHECK ADD CONSTRAINT [FK_ALUNOS_VENDEDORES] FOREIGN KEY([VENDEDOR])
REFERENCES [dbo].[VENDEDORES] ([CODIGO])
GO
ALTER TABLE [dbo].[ALUNOS] CHECK CONSTRAINT [FK_ALUNOS_VENDEDORES]
GO
ALTER TABLE [dbo].[ALUNOS] ADD CONSTRAINT [DF_ALUNOS_CANCELADO] DEFAULT ('N') FOR [CANCELADO]
GO
Table Presenca:
/****** Object: Table [dbo].[PRESENCA] Script Date: 08/24/2009 15:48:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRESENCA](
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[COD_ALUNO] [int] NOT NULL,
[TIPO_AULA] [int] NOT NULL,
[REPOSICAO] [int] NOT NULL,
[TEMPO] [float] NOT NULL,
[PROFESSOR] [int] NULL,
[DATA_HORA_ENTRADA] [datetime] NOT NULL,
[DATA_HORA_SAIDA] [datetime] NULL,
[COMPUTADOR] [varchar](50) NOT NULL,
[CURSO_ATUAL] [int] NOT NULL,
CONSTRAINT [PK_PRESENCA] PRIMARY KEY CLUSTERED
(
[CODIGO] 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
Table Simuladores:
/****** Object: Table [dbo].[SIMULADORES] Script Date: 08/24/2009 15:48:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SIMULADORES](
[CODIGO] [int] NOT NULL,
[CURSO] [nvarchar](100) NOT NULL,
[TIPO] [int] NOT NULL,
[ATIVIDADES] [int] NOT NULL,
[LIVRO] [nvarchar](200) NULL,
[ATIVIDADE_01] [nvarchar](200) NULL,
[MAESTRO] [nvarchar](200) NULL,
[TIPO_CURSO] [int] NOT NULL,
[PROGRAMA] [nvarchar](50) NULL,
[LOCAL_PROGRAMA] [int] NOT NULL,
[SIMULADOR] [nchar](1) NOT NULL,
[CARGA_HORARIA] [int] NULL,
CONSTRAINT [PK_SIMULADORES] PRIMARY KEY CLUSTERED
(
[CODIGO] 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
ALTER TABLE [dbo].[SIMULADORES] ADD CONSTRAINT [DF_SIMULADORES_SIMULADOR] DEFAULT ('S') FOR [SIMULADOR]
GO
Table Horario:
/****** Object: Table [dbo].[HORARIO] Script Date: 08/24/2009 15:49:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HORARIO](
[CODIGO] [int] IDENTITY(1,1) NOT NULL,
[COD_ALUNO] [int] NOT NULL,
[DIA] [int] NOT NULL,
[HI] [nchar](5) NOT NULL,
[HI_N] [float] NOT NULL,
[HF] [nchar](5) NOT NULL,
[HF_N] [float] NOT NULL,
[SALA] [int] NOT NULL,
[TIPO] [int] NOT NULL,
CONSTRAINT [PK_HORARIO] PRIMARY KEY CLUSTERED
(
[CODIGO] 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
ALTER TABLE [dbo].[HORARIO] WITH CHECK ADD CONSTRAINT [FK_HORARIO_ALUNOS] FOREIGN KEY([COD_ALUNO])
REFERENCES [dbo].[ALUNOS] ([CODIGO])
GO
ALTER TABLE [dbo].[HORARIO] CHECK CONSTRAINT [FK_HORARIO_ALUNOS]
GO
ALTER TABLE [dbo].[HORARIO] WITH CHECK ADD CONSTRAINT [FK_HORARIO_SALAS] FOREIGN KEY([SALA])
REFERENCES [dbo].[SALAS] ([CODIGO])
GO
ALTER TABLE [dbo].[HORARIO] CHECK CONSTRAINT [FK_HORARIO_SALAS]
GO
My problem is that I wanna list all data from table Alunos even with no data on table Horario.
August 24, 2009 at 12:59 pm
Sample Data? Expected Results?
August 24, 2009 at 1:13 pm
You're doing the LEFT OUTER join correctly. What is tripping you up is what you are leaving in the WHERE clause.
Leaving
H.SALA 0
means that only those rows that have a value in H.SALA will qualify (and only those where the value is 0). H.SALA cannot be null because of that. This will implicity turn the OUTER join into an INNER JOIN.
You should try promoting that criterion into the ON clause (it's designed to limit what is on the "right" without afecting what is on the "left"). This is one of those cases where you get some control over what you meant when creating the join.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2009 at 1:15 pm
In other words:
SELECT A.NOME,
A.MATRICULA,
CASE P.TIPO_AULA
WHEN 1 THEN 'REPOSIÇÃO/ADIANTAMENTO'
WHEN 2 THEN 'INFORMÁTICA'
WHEN 3 THEN 'DIGITAÇÃO'
WHEN 4 THEN 'AULA GRÁTIS'
END AS TIPAO,
P.DATA_HORA_ENTRADA,
P.DATA_HORA_SAIDA,
P.TEMPO,
S.CURSO
FROM PRESENCA P
INNER JOIN ALUNOS A ON P.COD_ALUNO = A.CODIGO
LEFT OUTER JOIN SIMULADORES S ON P.CURSO_ATUAL = S.CODIGO --NEED HELP HERE
LEFT OUTER JOIN HORARIO H ON H.COD_ALUNO = A.CODIGO --NEED HELP HERE
AND H.SALA 0
WHERE A.CODIGO 0--this is the "left" side, so this can safely stay in the WHERE clause
ORDER BY A.NOME
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2009 at 1:49 pm
I did using the SQL Server.... I right-clicked 'View' and choosed 'New view'. There I've mounted visually my query and saw the result... now I understood!
Thanks everybody for helpning!
August 24, 2009 at 1:51 pm
Sorry, I forgot the final result:
SELECT A.NOME,
A.MATRICULA,
CASE P.TIPO_AULA
WHEN 1 THEN 'REPOSIÇÃO/ADIANTAMENTO'
WHEN 2 THEN 'INFORMÁTICA'
WHEN 3 THEN 'DIGITAÇÃO'
WHEN 4 THEN 'AULA GRÁTIS'
END AS TIPAO,
P.DATA_HORA_ENTRADA,
P.DATA_HORA_SAIDA,
P.TEMPO,
S.CURSO,
H.SALA
FROM ALUNOS A INNER JOIN PRESENCA P ON A.CODIGO = P.COD_ALUNO
LEFT OUTER JOIN HORARIO H ON A.CODIGO = H.COD_ALUNO
LEFT OUTER JOIN SIMULADORES S ON P.CURSO_ATUAL = S.CODIGO
WHERE A.CODIGO 0
AND ((H.SALA 0)
OR (H.SALA IS NULL))
ORDER BY A.NOME, P.DATA_HORA_ENTRADA
August 24, 2009 at 2:25 pm
willian (8/24/2009)
Sorry, I forgot the final result:
SELECT A.NOME,A.MATRICULA,
CASE P.TIPO_AULA
WHEN 1 THEN 'REPOSIÇÃO/ADIANTAMENTO'
WHEN 2 THEN 'INFORMÁTICA'
WHEN 3 THEN 'DIGITAÇÃO'
WHEN 4 THEN 'AULA GRÁTIS'
END AS TIPAO,
P.DATA_HORA_ENTRADA,
P.DATA_HORA_SAIDA,
P.TEMPO,
S.CURSO,
H.SALA
FROM ALUNOS A INNER JOIN PRESENCA P ON A.CODIGO = P.COD_ALUNO
LEFT OUTER JOIN HORARIO H ON A.CODIGO = H.COD_ALUNO
LEFT OUTER JOIN SIMULADORES S ON P.CURSO_ATUAL = S.CODIGO
WHERE A.CODIGO 0
AND ((H.SALA 0)
OR (H.SALA IS NULL))
ORDER BY A.NOME, P.DATA_HORA_ENTRADA
Review what Matt posted - as his solution and your final solution are equivalent. I bet they even come up with the same execution plan.
Personally, I prefer Matt's approach - because it is very clearly outlined in the join criteria that you only want those rows where the SALA column is not equal to 0. It also simplifies the where clause...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply