Trouble with SQL joins

[HexElffilter]

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.

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.

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

 

[Stretch]

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???

Also do you understand the difference between an inner join, outer join and cross join? I very much doubt you'll need to use cross joins in this instance. It sounds more likely you need to use a few outer joins, though it's hard to see where because I'm not sure what data you have in which tables.

I'd start by only using only inner joins

in the format INNER JOIN table2 ON table2.value = table1.value

with no constraints and check carefully that you have all the data you'd expect with no duplicates and no missing rows. If you don't, come back and tell us what’s wrong. If it all looks ok then start adding the constraints one by one until you get precisely the results set you need.

 

[HexElffilter]

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:

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:

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.

 

[Stretch]

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.

It sounds like you're joining on the wrong column, the keys do not match or data types are incompatible.


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

INNER JOIN QUERCUS..QUERCUS.STUDENT_COURSE_DETAIL AS student_course_detail ON course_instance.OBJECT_ID = student_course_detail.COURSE_INSTANCE

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?

EDIT:

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?

 

[HexElffilter]

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

Yes

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.

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.

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.

 

[SimonCHere]

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.

find the OBJECT_ID of the rows for year 2008 and bring them back in a separate query and examine the data in the ACADEMIC_YEAR field

it could be as simple as having a trailing space

 

[HexElffilter]

Thanks for your helps guys.

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