Self join or ?? - 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

Transact-SQL (2000)

Self join or ??

Author

Topic

divided
Starting Member

12 Posts

Posted - 2007-06-13 : 16:30:04

So I have a table ACADEMIC, and in that table there is a column academic_session that has a value of either 'main' or ' ' Everyone has 2 records, one record with academic_session='main' and another identical record, except academic_session= ' ' I need to write a query to verify that noone has an academic_session='main' and NOT an associated academic_session= ' '. For example person1 will have 2 records, one with academic_session='main' and one with academic_session= ' ' and person2 has only ONE record with academic_session='main' This is the person i need to find. So far ive got this, what am i missing?

select distinct a1.people_code_id
,a1.academic_year
,a1.academic_term
,a1.academic_session
FROM academic a1, academic a2
WHERE a1.people_code_id = a2.people_code_id

I need to figure out how to code a1.academic_session='main' and/not a1.academic_session = ' ' ??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-13 : 16:34:20

divided
Starting Member

12 Posts

Posted - 2007-06-13 : 16:39:43

select distinct a1.people_code_id
,a1.academic_year
,a1.academic_term
,a1.academic_session
FROM academic a1
INNER JOIN academic a2
ON a1.people_code_id = a2.people_code_id
WHERE a1.academic_session = 'main'
AND a1.academic_session <> ''
and a1.academic_term='summer'
and a1.academic_year='2007'

Thanks for the quick reply. I need to narrow it down to only include summer 2007, problem i ran into is this, there "should" be a another record academic_session='', BUT if there isnt, then the only record there is one with academic_session='main' and nothing more. This makes it impossible to search based on academic_session='' because if they dont have one, its ONLY academic_session='main'

divided
Starting Member

12 Posts

Posted - 2007-06-13 : 16:45:41

Problem with the query you provided is that it assumes there is a academic_session='' value, when in fact if that value is missing (which is what i need to find) then the only record will have academic_session='main'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-13 : 16:47:19

You need to provide a data example of what your data looks like and what you want returned.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

divided
Starting Member

12 Posts

Posted - 2007-06-13 : 16:56:38

people_code_id academic year academic term academic session

P000005555 2007 SUMMER MAIN
P000005555 2007 SUMMER
P000006666 2007 SUMMER MAIN
P000006666 2007 SUMMER
P000007777 2007 SUMMER MAIN

In this example the first 2 records with people code id's of p000005555 and p000006666 are fine, the record i want to run the query for and return is the person with people code id p000007777 and ONLY that person because he has a academic_session='main' record, but that all. As p000006666 has a academic_session='main' AND academic_session=''

divided
Starting Member

12 Posts

Posted - 2007-06-13 : 17:14:16

I found my answer, this is what i came up with.

select distinct a1.people_code_id
,a1.academic_year
,a1.academic_term
,a1.academic_session
FROM academic a1
WHERE a1.academic_session = 'main'
and a1.academic_term='summer'
and a1.academic_year='2007'
and not exists (select *
FROM academic a2
WHERE a2.academic_session = ''
and a2.academic_term='summer'
and a2.academic_year='2007'
and a1.people_code_id = a2.people_code_id)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources