LEFT JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

LEFT JOIN

Author

Topic

JimiC
Starting Member

29 Posts

Posted - 2003-05-16 : 11:57:13

firstly, i apologise for not having researched this as well as i could have. im just _extremely_ short of time and need a bit of a pointer to know if im going about this in the right way.

im fairly new to a great deal of SQL, ive spent quite some time with basic SELECTs, UPDATEs, etc. but theres clearly a lot more to it than that.

the situation:

the four tables im dealing with here are data_Curso_Capitulos (Chapters), data_Curso_Themes (themes), data_User_Cursos (reference of what users are in what course), and data_Alumno_Cursos_Registro(a log of completed themes)

what i want to return is a complete set of chapters and themes based on the course id (@CursoID), and, if it exists in the log table (data_Alumno_Cursos_Registro) the data specific to the user id (@UsuarioID)

the code:

CREATE PROCEDURE sp_GetCourseHistory
(
@CursoID Int,
@UsuarioID Int
)

AS
SELECT
CC.Capitulo_ID,
CC.Capitulo_Nombre,
CT.Tema_ID,
CT.Tema_Nombre,
ACR.Tema_Completado_Fecha,
ACR.Tema_Includes_Exam,
ACR.Tema_Completado_Nota
FROM
data_Curso_Capitulos CC
INNER JOIN
data_Curso_Temas CT
ON
CC.Capitulo_ID = CT.Capitulo_ID
LEFT JOIN
data_Alumno_Cursos_Registro ACR
ON
ACR.Tema_ID = CT.Tema_ID
ORDER BY
CC.Capitulo_ID,
CT.Tema_ID
GO

one extremely encouraging point is that the syntax check works :D

as i said, sorry to come rushing straight in without a clear point to raise, but ive looked at a heap of examples, _think_ ive written something that _could_ work and would just like to know if this is going to work.

i just dont have time to spend days struggling with this only to find i went about it the wrong way :-/

thanks in advance (and i promise if i can get all this working in the next couple of weeks ill do some serious reading before posting again ;))

Making things work by dint of dinting.

JimiC
Starting Member

29 Posts

Posted - 2003-05-16 : 12:05:41

see how spaced out i am today ?

i was so busy looking at the joins i forgot my input variables ...

2nd go:

CREATE PROCEDURE sp_GetCourseHistory
(
@CursoID Int,
@UsuarioID Int
)

AS
SELECT
CC.Capitulo_ID,
CC.Capitulo_Nombre,
CT.Tema_ID,
CT.Tema_Nombre,
ACR.Tema_Completado_Fecha,
ACR.Tema_Includes_Exam,
ACR.Tema_Completado_Nota
FROM
data_Curso_Capitulos CC
INNER JOIN
data_Curso_Temas CT
ON
CC.Capitulo_ID = CT.Capitulo_ID
AND CC.Curso_ID = @CursoID
LEFT JOIN
data_Alumno_Cursos_Registro ACR
ON
ACR.Tema_ID = CT.Tema_ID
INNER JOIN
data_User_Cursos UC
ON
UC.User_Curso_ID = ACR.Alumno_Curso_ID
AND UC.Usuario_ID = @UsuarioID
ORDER BY
CC.Capitulo_ID,
CT.Tema_ID
GO

Making things work by dint of dinting.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-16 : 14:00:11

You also need to post the DDL for your tables.

And why didn't you add the themes table to your join?

Brett

8-)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-17 : 11:39:57

And some sample data

JimiC
Starting Member

29 Posts

Posted - 2003-05-19 : 07:26:09

k, well here we are on monday morning .. i see the way i should post here now ..

i take it thats so that anyone can reproduce exactly what youre doing eh ?

anyway, what with it being monday, (and having thought about it a bit over the weekend) i took the time to insert some test data and tried the procedure, at first it was filtering out themes that didnt have a "completed" record associated in the ACR table.

changing the INNER JOINs to LEFT JOINs ive achieved something very close to perfect, and feel fairly confident that before lunch this will be working correctly

if it goes horribly wrong ill be back with the table design and some sample data, if not then next time i have a problem ill post it correctly to start with ;)

thanks

Making things work by dint of dinting.

JimiC
Starting Member

29 Posts

Posted - 2003-05-19 : 07:30:50

working ;)

Making things work by dint of dinting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources