One more question

[wylliecoyote]

I am inserting this into my SQL command and it is making my formula redundant since there are 6 null rows. How do I do this so it will only use one the min date_hired field where there is a null date_released field?

lecturer.date_hired < (select min(attendance.regstrn_made_date) from attendance)
and
lecturer.date_released is NULL

 

[r937]

not enough context

r937.com | rudy.ca

 

[wylliecoyote]

When I put that int othe where command below, it will return the count as 6, instead of 1 since there are 6 null fields. It will than cause the total to be 6 for the count of the number of courses taken, and 6*amount_paid for the sum. I only want it to compare to the minimum date_hired.

select student_id as "StudentId", sum(amount_paid) as "Total_Paid",
min(regstrn_made_date) as "Earliest_Reg_Date",
count(*) as "Number_of_courses", avg(amount_paid) as "Average_Paid" from attendance
where offering_id in ('9111','9112','9212')
group by student_id
order by "Total_Paid" desc;

 

[r937]

sorry, i still don't get it

you can't add the code in your first post to the code in last post because the last post does not have the lecturer table in the FROM clause

r937.com | rudy.ca

 

[SkipVought]

This statement

lecturer.date_hired < (select min(attendance.regstrn_made_date) from attendance)
and
lecturer.date_released is NULL

implies that you ALSO have a table named lecturer.

If so, attendance and lecturer must be properly joined.

?????


Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[wylliecoyote]

Here is the complete code. I do have a table named lecturer. If they aren't joined properly, how can I fix it so they are properly joined? Thanks.

select attendance.student_id as "StudentID", substr(student_fname,1,1) || '-' || student_lname as "Name",
company_name as "Comany_Name", sum(amount_paid) as "Total_Paid", min(regstrn_made_date) as "Earliest_Reg_Date",
count(*) as "Number_of_courses", avg(amount_paid) as "Average_Paid" from student, attendance, lecturer,
stud_company
where offering_id in ('9111','9112','9212')
and
lecturer.date_hired < (select min(attendance.regstrn_made_date) from attendance)
and
lecturer.date_released is NULL
and
student.student_id = attendance.student_id
and
student.company_id = stud_company.company_id
group by attendance.student_id, student_fname, student_lname, company_name
having avg(amount_paid) < 650
order by "Total_Paid" desc;

 

[r937]

your FROM and WHERE clauses can be re-written like this:

  from student
inner
  join attendance
    on student.student_id 
     = attendance.student_id
inner
  join stud_company
    on student.company_id 
     = stud_company.company_id
inner
  join lecturer
    on ????
 where offering_id in ('9111','9112','9212')
   and lecturer.date_hired 
     < (select min(attendance.regstrn_made_date) 
          from attendance)
   and lecturer.date_released is NULL

notice that there is no join condition that joins the lecturer table to the other three

the condition on lecturer.date_hired is not a join condition because the subquery on attendance is not the "same" attendance table as in the join

perhaps the attendance table should not be in the join?

r937.com | rudy.ca

 

[SkipVought]

Well you have to know what value(s) in one or more of your tables relates to what value(s) the lecturer table.

Otherwise, you will get a cartesian join where every row in lecturer will be joined to every row in the resultant joins in the other 3 tables.

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[wylliecoyote]

The only column that is the same is 'UPDATE_TS' which is the last time the row was updated. There are no matches between the lecturer table and the attendance table in this column.

 

[SkipVought]

Then, how can you assign lecturer data to anything in the other tables???????

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[wylliecoyote]

After talking with a friend he is suggesting that it could be an "exists" function.