June 13, 2007 at 2:25 pm
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
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'
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'
June 13, 2007 at 2:47 pm
Is this what you are looking for?
SELECT
a1.people_code_id
,a1.academic_year
,a1.academic_term
,a1.academic_session
FROM
academic a1
LEFT OUTER JOIN academic a2 ON
a1.people_code_id = a2.people_code_id
WHERE
a1.academic_session = 'main'
AND a2.academic_session IS Null
June 13, 2007 at 2:51 pm
That WONT work because if there missing academic_session='' then the ONLY record with academic_session would be academic_session='main' and nothing more. Thats what makes this a hard one to figure out.
June 13, 2007 at 2:57 pm
Try this
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)
June 13, 2007 at 3:10 pm
WOW! That was it, Thanks alot. You guys are great!!
June 13, 2007 at 4:15 pm
I need to find a little more information, and was hoping i could just alter my existing script. basically what i need is this, if there are indeed 2 records, academic_session='main' and academic_session='' THEN, (so after i run my first script) I need to compare the values in the full_part column to verify that both records have the same values. Will the below script work for me?
select distinct first_name
,last_name
,a1.people_code_id
,a1.academic_year
,a1.academic_term
,a1.academic_session
FROM academic a1
inner join people p
on p.people_code_id=a1.people_code_id
WHERE a1.academic_session = 'main'
and a1.full_part in ('full', 'less', 'half')
and a1.academic_year='2007'
and not exists (select *
FROM academic a2
WHERE a2.academic_session = ''
and a2.full_part in ('full', 'less', 'half')
and a2.academic_year='2007'
and a1.people_code_id = a2.people_code_id)
June 13, 2007 at 4:18 pm
...
and a2.full_part = a1.full_part
and a2.academic_year = a1.academic_year
...
_____________
Code for TallyGenerator
June 13, 2007 at 4:33 pm
Now i need to find the status of the enroll_separation column and make sure they are in sync, if they are NOT then i need them to be on this report, this is what i came up with (altering the previous query) but it doesnt work, what am i missing here?
select distinct first_name
,last_name
,a1.people_code_id
,a1.academic_year
,a1.academic_term
,a1.academic_session
,enroll_separation
FROM academic a1
inner join people p
on p.people_code_id=a1.people_code_id
WHERE a1.academic_session = 'main'
and a1.enroll_separation in ('enrl', 'adm', 'loa','with')
and a1.academic_year='2007'
and not exists (select *
FROM academic a2
WHERE a2.academic_session = ''
and a2.enroll_separation in ('enrl', 'adm', 'loa','with')
and a2.academic_year='2007'
and a1.people_code_id = a2.people_code_id
and a1.enroll_separation = a2.enroll_separation)
June 13, 2007 at 5:31 pm
In which case the LEFT OUTER JOIN should return a null value for the right hand side. While I don't have test data from you, so I haven't tested it, unless I'm misunderstanding you, I'm not seeing a problem with the code.
Did you run it?
June 15, 2007 at 11:00 am
Am I missing something here? Did the OP claim that the table held pairs of tuples for every... um, entry -- each identical except that one attribute held a constant value in one row and an empty value in the other? Is this an example of WNF (Weird Normal Form)? What information does the second tuple give us that is not already contained in the first? If there is some other information, why isn't it part of the first record or, if normalization required, moved to another table?
What is really hard to figure out is why go thru all this effort to make poor data modeling work?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply