Help with SubQuery!

[cantona]

Help! Im stuck trying to produce a query which needs to use sub queries. This is an area where I always seem to struggle! Basically, im trying to produce a query that will display for a learner, the course code, the course start date, and register information related to that course. The difficulty im having is that registers are not always linked to the course code, some registers are linked to a code which may have more than one course code above it…..

The data is held in the following areas,

Course_Learner

Learner_ID – Example Data - 120120
Course_code – Example Data – ABC1

Registers

Register_ID, - Example Data – R123
Reg_Course_Code – A1
Reg_Date – 03/09/2007
Learner_ID - 120120

Course_Details

Course_code, Example Data = ABC1
Child_course_code Example Data = A1

The format I would like to produce is the following….

Learner_ID, Example Data - 120120
Course_Code, - Example Data ABC1
Course_Start_Date, - Example Data – 01/09/2007
Count (Register_ID) – 7
Min (Register_Date) – 02/09/2007

The problem im having is that I only want to count registers for a learner if the register course code is either a child or parent course code. For example, course code ABC1 may have 3 child codes underneath it, A1,A2 and A3. The registers for course code ABC1 may be assigned to any of the four possible codes. Within the Registers table a leaner may have a number of registers that they appear on but I only want to count the ones that are part of the course from course_learner. What would be the easiest way of achieving this?

 

[SantaMufasa]

Cantona,

I'm certain we can help you. The problem is that there seem to be anomalies/discrepancies/issues amongst your sample data, the description of your need, and the output:[ul][li]If the "Course_Start_Date" is 01/09/2007, how could the "Min Register_Date" be 02/09/2007?[/li][li]Since I don't see it explicitly in your data, please explain the correct derivation of "Min Register_Date".[/li][li]How did "Count (Register_ID)" become "7". Are there six more REGISTER rows for this LEARNER and COURSE that we don't see in your sample data?[/li][li]Your sample output appears "vertical" instead of the standard "horizonal" output for a row. Is vertical output of the row a requirement of this problem?[/li][/ul]

So, to help us help you better, could you please:[ul][li]Post the responses to the above questions, and[/li][li]Post the "CREATE TABLE..." statements (three tables-worth) and "INSERT INTO..." statements (for all three tables) that we can use to yield the results for which you are looking.[/li][/ul]

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cantona]

Hi SantaMufasa,

Thanks for your response.. I'll try and explain better what i mean!
I suppose the register date being different to the start date is the main reason why the report is required. It will be used to identify anomolies. We have a number of course dates that are way before the first register date, which obviously shouldnt be the case.
The example data i provided off the top of my head to give an idea of the type of data that each field contains. A learner can indeed appear on multiple registers. The data isnt in a vertial format. i typed it out like that as i was struggling to get the data to appear in a reasonable format in columns. Here is the SQL from the query that i have produced so far...

SELECT

cl.funding_year,
cl.Learner_id,
cl.Course_Code,
cl.start_date,
Count(r.register_id) AS Registers,
Min(r.reg_date) AS Reg_Date

FROM

Course_Learner cl
Registers r

WHERE

cl.learner_ID = r.learner_id


GROUP BY

cl.funding_year,
cl.Learner_id,
cl.Course_Code,
cl.start_date

HAVING

cl.funding_year = '14'

This allows me to see the earliest register date a person has and lets me compare it to the start date of the course. However, as it stands, the min(reg_date) is giving me the earliest reg date a person has. this may be for a register unrelated to the particular course im looking at. I need it to only count registers linked to the cl.course_code. Its here where im struggling.. I cant simply link the cl.course_code to registers.course_code as they dont always match. For example a course code ABC1 may have child course codes that make up the main course code. Some registers maybe linked to the child codes rather than the main code.The course info is held in a separate table course_details as follows;

Parent_Course child_course
ABC1 A1
ABC1 A2
ABC1 A3
ABC2
ABC3
ABC4 B1

If looking at Course ABC1, i would need to search for registers linked to ABC1, A1, A2 and A3.

Hope this makes more sense??!

 

[SantaMufasa]

Cantona,

That certainly sounds reasonable. So that I can provide you with an appropriate answer that matches both your expectations and your data, could you please (as I requsted earlier), post both "CREATE TABLE..." and "INSERT INTO..." statements that results in a reasonable test bed of data?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]