sql 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 2005 Forums

Transact-SQL (2005)

sql left join

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-01 : 05:34:19

Hi,

Im having problems with a outer join, im trying to subtract (take out the differences between 2 tables).

I have 2 register tables and trying to pick out which register hasnt got any marks, and since cant really use where code = null. Im using left outer join function.

Query1 left outer Query2 on acad_period, register_id, register_group, week_no. query1 has all the registers listed, and query2 has all the registers with marks on them. Im using left outer join but it doesnt bring back anything. I tried using NOT IN statement but cant use it for more than one field? Any ideas?

Query 1

SELECT acad_period, register_id, register_group, week_no, day_of_week
FROM A
WHERE acad_period = '08/09'

Query 2

SELECT acad_period, register_id, register_group, week_no, day_of_week, dbo.sttdstud.absence_code
FROM A
WHERE acad_period = '08/09'

thanks cipriani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 05:38:05

can you show your left join query?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-01 : 05:42:08

quote:
Originally posted by visakh16

can you show your left join query?

select A.acad_period, A.register_id, A.register_group, A.week_no, A.day_of_week
from (SELECT acad_period, register_id, register_group, week_no, day_of_week
FROM A
WHERE acad_period = '08/09') AS A LEFT OUTER JOIN
(SELECT acad_period, register_id, register_group, week_no, day_of_week, dbo.sttdstud.absence_code
FROM A
WHERE acad_period = '08/09') AS B ON A.acad_period = B.acad_period AND A.register_id = B.register_id AND
A.register_group = B.register_group AND A.week_no = B.week_no AND A.day_of_week = B.day_of_week

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 05:49:58

is dbo.sttdstud.absence_code
another table?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-01 : 05:51:11

quote:
Originally posted by visakh16

is dbo.sttdstud.absence_code
another table?

Sorry ignore that, just modifying the query so it can be easily interpreted.

I dont get it, isnt left outer join suppsed to bring everything back thats not in the other table?

select A.acad_period, A.register_id, A.register_group, A.week_no, A.day_of_week
from (SELECT acad_period, register_id, register_group, week_no, day_of_week
FROM A
WHERE acad_period = '08/09') AS A LEFT OUTER JOIN
(SELECT acad_period, register_id, register_group, week_no, day_of_week, absence_code
FROM A
WHERE acad_period = '08/09') AS B ON A.acad_period = B.acad_period AND A.register_id = B.register_id AND
A.register_group = B.register_group AND A.week_no = B.week_no AND A.day_of_week = B.day_of_week

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 06:13:39

Yup. it is supposed to do that.what does below return you?

SELECT acad_period, register_id, register_group, week_no, day_of_week
FROM A
WHERE acad_period = '08/09'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources