Trouble with SQL joins

HexElffilter · 10 Dec 2008 at 10:21

Hi folks,

Hope someone can help me here with some SQL joins.

At work I am trying to convert an access based report to a sql server report, but I'm having difficulty with the joins.

Here is the structure of the tables in access...

I have tried a couple of things in SQL Server firstly I joined all the tables with inner joins, but then when I ran the query I got no records returned.

Code:

SELECT     (CASE WHEN department.DEPARTMENT = 'HORTICULTURE_WBL' THEN 'HORTICULTURE_PT' ELSE department.DEPARTMENT END) AS Dept, 
                      course.COURSE, course.DESCRIPTION, course_instance.ACADEMIC_YEAR, (CASE WHEN mode_of_study.MODE_OF_STUDY IN ('FULL-TIME_FULL_Y', 
                      'SANDWICH', 'FULL-TIME_LESS_T', 'FTS', '04') THEN 'FT' ELSE 'PT' END) AS Mode, person.ID_NUMBER, person.FIRST_NAME, person.SURNAME, 
                      person.DATE_OF_BIRTH, student_course_detail.TRANSFER_DATE, withdrawal_reason.DESCRIPTION AS Withdrawal_Reason, 
                      address.ADDRESS_LINE2, address.ADDRESS_LINE3, address.ADDRESS_LINE4, address.ADDRESS_LINE5, address.ADDRESS_LINE6
FROM         QUERCUS..QUERCUS.STUDENT_COURSE_DETAIL AS student_course_detail INNER JOIN
                      QUERCUS..QUERCUS.STATUS AS status ON student_course_detail.STATUS = status.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.STUDENT_WITHDRAWAL_REASON AS student_withdrawal_reason ON 
                      student_course_detail.OBJECT_ID = student_withdrawal_reason.STUDENT_COURSE_DETAIL INNER JOIN
                      QUERCUS..QUERCUS.WITHDRAWAL_REASON AS withdrawal_reason ON 
                      student_withdrawal_reason.WITHDRAWAL_REASON = withdrawal_reason.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.PERSON AS person ON student_course_detail.PERSON = person.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.ADDRESS AS address ON person.ADDRESS = address.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.COURSE_INSTANCE AS course_instance ON 
                      student_course_detail.COURSE_INSTANCE = course_instance.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.MODE_OF_STUDY AS mode_of_study ON course_instance.MODE_OF_STUDY = mode_of_study.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.COURSE AS course ON course_instance.COURSE = course.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.DEPARTMENT AS department ON course.DEPARTMENT = department.OBJECT_ID
WHERE     (course_instance.ACADEMIC_YEAR = 2008) AND (status.STATUS = 'PW')

Also I tried linking the tables in the WHERE clause, which changed all the joins to CROSS JOINS. This worked, but when I went to add a filter to the academic year again it returned no records, even though the filtered data was returned before I added the filter. :confused:

Code:

SELECT     TOP (100) PERCENT (CASE WHEN qd.DEPARTMENT = 'HORTICULTURE_WBL' THEN 'HORTICULTURE_PT' ELSE qd.DEPARTMENT END) AS Dept, 
                      qc.COURSE, qc.DESCRIPTION AS TITLE, qci.ACADEMIC_YEAR, qmos.MODE_OF_STUDY, qp.ID_NUMBER, qp.FIRST_NAME, qp.SURNAME, 
                      qscd.TRANSFER_DATE, qwr.DESCRIPTION AS WITHDRAWAL_REASON, qa.ADDRESS_LINE2, qa.ADDRESS_LINE3, qa.ADDRESS_LINE4, 
                      qa.ADDRESS_LINE5, qa.ADDRESS_LINE6, qs.STATUS
FROM         QUERCUS..QUERCUS.DEPARTMENT AS qd CROSS JOIN
                      QUERCUS..QUERCUS.COURSE AS qc CROSS JOIN
                      QUERCUS..QUERCUS.COURSE_INSTANCE AS qci CROSS JOIN
                      QUERCUS..QUERCUS.MODE_OF_STUDY AS qmos CROSS JOIN
                      QUERCUS..QUERCUS.STUDENT_COURSE_DETAIL AS qscd CROSS JOIN
                      QUERCUS..QUERCUS.STATUS AS qs CROSS JOIN
                      QUERCUS..QUERCUS.PERSON AS qp CROSS JOIN
                      QUERCUS..QUERCUS.ADDRESS AS qa CROSS JOIN
                      QUERCUS..QUERCUS.STUDENT_WITHDRAWAL_REASON AS qswr CROSS JOIN
                      QUERCUS..QUERCUS.WITHDRAWAL_REASON AS qwr
WHERE     (qd.OBJECT_ID = qc.DEPARTMENT) AND (qc.OBJECT_ID = qci.COURSE) AND (qci.MODE_OF_STUDY = qmos.OBJECT_ID) AND 
                      (qci.OBJECT_ID = qscd.COURSE_INSTANCE) AND (qscd.STATUS = qs.OBJECT_ID) AND (qscd.PERSON = qp.OBJECT_ID) AND 
                      (qp.ADDRESS = qa.OBJECT_ID) AND (qscd.OBJECT_ID = qswr.STUDENT_COURSE_DETAIL) AND (qswr.WITHDRAWAL_REASON = qwr.OBJECT_ID) AND 
                      (qs.STATUS = 'PW')
ORDER BY qci.ACADEMIC_YEAR DESC

Also these tables are all in an oracle database via a linked server instance (hence the QUERCUS..QUERCUS).

Am I right in thinking I may need a mixture of joins to get this to work?

Thanks

HexElffilter · 10 Dec 2008 at 12:58

Stretch said:
For a start why are you trying to convert an Access report into a SQL Server report if all the data is in an Oracle database???

The reason for this is because we are developing a web based system (ASP.NET) for our reports. At the moment we have to run the reports each week in Access and save them to PDF. We want to create a real-time reporting site using sql server reporting services.

As for where the query fails, it is when trying to add the 'student_course_detail' table. As soon as this table is added no records are returned.

This works:

Code:

SELECT     (CASE WHEN department.DEPARTMENT = 'HORTICULTURE_WBL' THEN 'HORTICULTURE_PT' ELSE department.DEPARTMENT END) AS Dept, 
                      course.COURSE, course.DESCRIPTION, course_instance.ACADEMIC_YEAR, (CASE WHEN mode_of_study.MODE_OF_STUDY IN ('FULL-TIME_FULL_Y', 
                      'SANDWICH', 'FULL-TIME_LESS_T', 'FTS', '04') THEN 'FT' ELSE 'PT' END) AS Mode
FROM         QUERCUS..QUERCUS.COURSE_INSTANCE AS course_instance INNER JOIN
                      QUERCUS..QUERCUS.MODE_OF_STUDY AS mode_of_study ON course_instance.MODE_OF_STUDY = mode_of_study.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.COURSE AS course ON course_instance.COURSE = course.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.DEPARTMENT AS department ON course.DEPARTMENT = department.OBJECT_ID
WHERE     (course_instance.ACADEMIC_YEAR = 2008)

This doesn't work:

Code:

SELECT     (CASE WHEN department.DEPARTMENT = 'HORTICULTURE_WBL' THEN 'HORTICULTURE_PT' ELSE department.DEPARTMENT END) AS Dept, 
                      course.COURSE, course.DESCRIPTION, course_instance.ACADEMIC_YEAR, (CASE WHEN mode_of_study.MODE_OF_STUDY IN ('FULL-TIME_FULL_Y', 
                      'SANDWICH', 'FULL-TIME_LESS_T', 'FTS', '04') THEN 'FT' ELSE 'PT' END) AS Mode
FROM         QUERCUS..QUERCUS.COURSE_INSTANCE AS course_instance INNER JOIN
                      QUERCUS..QUERCUS.MODE_OF_STUDY AS mode_of_study ON course_instance.MODE_OF_STUDY = mode_of_study.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.COURSE AS course ON course_instance.COURSE = course.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.DEPARTMENT AS department ON course.DEPARTMENT = department.OBJECT_ID INNER JOIN
                      QUERCUS..QUERCUS.STUDENT_COURSE_DETAIL AS student_course_detail ON 
                      course_instance.OBJECT_ID = student_course_detail.COURSE_INSTANCE
WHERE     (course_instance.ACADEMIC_YEAR = 2008)

If I change the join to 'student_course_detail' to a LEFT OUTER JOIN and add one of the fields that is in that table, all the values for that field are null. Which I know is wrong, as they are populated.
If I do a RIGHT OUTER JOIN then no records are returned.

HexElffilter · 10 Dec 2008 at 14:53

Stretch said:
So it's adding this line that causes the problem?

Yes

Stretch said:
Can you give the table definitions for course_instance and student_course_detail? Can you also provide a sample of data from each table that should join i.e. the join column values OBJECT_ID and COURSE_INSTANCE match?

The primary key field in the tables (object_id) is a number field, the foreign key in this case (course_instance) is also a number field. As an example both the fields contain a value of 76983.

Stretch said:
Have you also tried writing a small stand alone query inner joining course_instance and student_course_detail, selecting only key column values from each table to see if that works?

I've also tried this and got no records returned. Here is the sql I used and tried to return the field I was going to use in the final query.

Code:

SELECT     student_course_detail.TRANSFER_DATE
FROM         QUERCUS..QUERCUS.STUDENT_COURSE_DETAIL AS student_course_detail INNER JOIN
                      QUERCUS..QUERCUS.COURSE_INSTANCE AS course_instance ON student_course_detail.COURSE_INSTANCE = course_instance.OBJECT_ID
WHERE     (course_instance.ACADEMIC_YEAR = 2008)

Maybe this issue is deeper than just the SQL i.e. something to do with server settings.

EDIT: Now this is weird. If I remove the filter on academic_year it brings back records for all years and includes the transfer_date even for some in year 2008.
If I add the filter back it returns nothing again.

HexElffilter · 11 Dec 2008 at 10:56

Thanks for your helps guys.

I tried it in a stored procedure yesterday and it seems to work perfectly this way.