MYSQL Query from 2 tables

[gabor]

I'm building a test management system. I have 2 tables in the same database:

qstn_langart_tbl and test_results_tbl. qstn_langart_tbl stores all the available questions while test_results_tbl has the students' test results.

The fields in the tables:

 

qstn_langart_tbl

Field                             Type           Null      Explanation

code                          varchar(12)      No      unique code for the question (e.g. LA-0001-0001)

descr                         varchar(255)     No      brief description (e.g. Comprehension, Tasmanian Devils)

CA_stnd                      int(9)              No      CA state standard number (not used right now)

test                           char(            No      Assessment code (e.g. T-0001)

test2                         char(            No      Assessment code (e.g. T-0001)

test3                     char(                No      Assessment code (e.g. T-0001)

type                        varchar(20)        No      Question type (e.g. Multiple choice)

Flesch_Kinkaid         decimal(10,1)        No      Flasch-Kinkaid Reading level (e.g. 5.7)

num_of_qstns            int(3)                No      Number of sub questions included under the same code

 

 

test_results_tbl

Field                         Type                   Null                  Description

student_id                 int(9)                    No                  Unique student ID (e.g. 3421)

test_code                 varchar(13)             No                  unique code for the question (e.g. LA-0001-0001)

version                      varchar(2)             No                  Test version (e.g. A)

test_result                  int(5)                   No                  Test result (e.g. 75 which means 75%)

completed                varchar(3)                No                 Is the test result satisfactory? (e.g. yes)

test_date                   date                     No                   Date (e.g. 2007-09-16)

 

My goal is to list those test codes per student ID which are not completed by a specific student which means I have to print those codes from qstn_langart_tbl which are not listed  in test_results_tbl under a specific student ID.

 

For example I have these codes in qstn_langart_tbl:

LA-0001-0001

LA-0001-0002

LA-0001-0003

LA-0001-0004

LA-0001-0005

 

Student with ID# 4512 completed LA-0001-0002 and LA-0001-0005.

I only want to list

LA-0001-0001

LA-0001-0003

LA-0001-0004

for this student as these are the test he/she has to complete.

 

I tried to use the following code:

SELECT  qstn_langart_tbl.code, qstn_langart_tbl.descr, qstn_langart_tbl.Flesch_Kinkaid, test_results_tbl.student_id, test_results_tbl.test_code FROM qstn_langart_tbl, test_results_tbl WHERE  student_id=4512 AND qstn_langart_tbl.code<>test_results_tbl.test_code AND test_code LIKE '%LA-0001%'

 

but this lists something completely different.

 

MySQL client version: 5.0.45

 

I would appreciate any help to achieve the results I need.

Thank you.

 

 

 

[aschk]

Perhaps something like this will do the job for you.

 

SELECT g.code 
FROM gstn_langart_tbl g
LEFT JOIN test_results_tbl t ON t.test_code = g.code AND t.student_id = 4512
WHERE t.test_code IS NULL
AND g.test LIKE 'LA-0001%'

 

Having not spent much time examining them, I don't believe your tables are normalised very well. I could be wrong of course.

[gabor]

Thank you. I'm tryng to make this work. At this time it does not return any records though it supposed to. Is it possible that I have to do the left join the other direction?

[aschk]

Any chance of a SQL dump from yourself so that I may test it locally? That statement "should" have worked. It selects ALL the records from the gstn_langart_tbl (i.e. all the tests) and LEFT JOINs then with the test_results_tbl based on code and student_id. So if no student_id and code correlate it should fill NULL in the fields (which is what you want).

 

Anywho, sql dump would be good.

[gabor]

Thank you for your help. How can I send you the 2 dump files?

[aschk]

I think you can send them in a personal message (via the forum) or you may even be able to attach them to your posting here.

[gabor]

Can I email it to you? The system doesn't allow me to send msgs and I can't see how to attach a file to the posting.

Thank you

[gabor]

Please check your email. Thank you.

[gabor]

I used the following code:

SELECT qstn_langart_tbl.* FROM qstn_langart_tbl

LEFT JOIN test_results_tbl ON test_results_tbl.test_code=qstn_langart_tbl.code WHERE test_results_tbl.student_id = 4578

AND test_results_tbl.test_code  LIKE 'LA-0001%' AND test_results_tbl.test_code IS NOT NULL

 

This returns all the tests the student completed.

 

If I use  test_results_tbl.test_code IS  NULL returns no results. Please help me to modify this to return all the tests not completed by the student.

 

Thank you.