[archive] SQL , Nested Selects and ODBC

[Migrated content. Thread originally posted on 15 June 2005]

I apologize if this isn't the right place but I couldn't find an SQL/ODBC forum. We are using Acucobol 6.1 ODBC for this through Excel

We are trying to do a select from two tables using ODBC. We have two tables (Files, Student Info and Award Info). The relationship between the two files is not a one key field but is actually three fields (First name, Last Name, student ID). We want to select * from the student info table where student_info.flag1 = Y and student_info.flag2 = Y and there isn't a match in the Award info. We tried to build the key by using the AS option:

rtrim(student_info.STU_LAST_NM) rtrim(student_info.STU_FIRST_NM) rtrim(student_info.STU_ID) as student_key

and create the key for the award the same way:

rtrim(award_info.STU_LAST_NM) rtrim(award_info.STU_FIRST_NM) rtrim(award_info.STU_ID) as award_key

The statement would look like:

SELECT * from student_info
rtrim(student_info.STU_LAST_NM) rtrim(student_info.STU_FIRST_NM) rtrim(student_info.STU_ID) as student_key
WHERE student_info.flag1 = Y and student_info.flag2 = Y and NOT IN (
SELECT * from award_info
rtrim(award_info.STU_LAST_NM) rtrim(award_info.STU_FIRST_NM) rtrim(award_info.STU_ID) as award_key)


Any help or pointers to help would be appreciated