[MySQL] SQL Guru needed...

A

apollod

Veteran XX
Alright.. i have this situation where I need to get data from 2 joined tables, where value X is present in column A on table2 but value Z is not present in column A on table2..

Basically here's the relevant info...

Table STUDENT:
primary_id
student_name
student_age
student_gradyear

Table TESTS:
primary_id
test_name
test_grade

The query thus far is:
Code: 
Select s.student_name, s.student_age, s.student_gradyear, t.test_name, t.test_grade
  FROM STUDENT as s
     INNER JOIN TESTS AS t ON (s.primary_id = t.primary_id)
  (insert 'WHERE' clause here)
  ORDER BY s.student_name;

Now I want to be able to select only those students who have the value of 'Test One' stored in the 'test_name' table with a test_grade value of 'A', but there is no value of 'Test Two' stored in the test_name column for that student.

I'm sure WHERE EXISTS and NOT EXISTS subqueries would be used.. but i'm just glitching out this morning and can't wrap my head around it.

Of course I could always just run two independent queries and then use a script to compare the results, but I much rather just have mysql do it in one fell swoop
 
Last edited:
WHERE STUDENT.test_grade= 'A' AND TESTS.test_name<> 'Test Two'


or something like that, i haven't written an sql query in literally a year
 
How much does this pay?

Let's talk salary first. Then I'll get around to answering your question.
 
and not exists (select t2.test_name where t2.test_name = 'Test Two' and t2.primary_id = t1.primary_id)
 
Big Monkey said:
WHERE t.test_name = 'Test One'
and t.test_grade = 'A'


?
Click to expand...

That would return valid results for 'Test One' and a grade of 'A' being present.. but it doesn't check to see if 'Test Two' exists for that student; so it would return results where a student has taken both 'Test One' and 'Test Two', but I want results where a student has taken 'Test One' with a grade of 'A' but hasn't taken 'Test Two' at all (thus a value of 'Test Two' won't exist in the TESTS table)
 
Last edited:
apollod said:
That would return valid results for 'Test One' and a grade of 'A' being present.. but it doesn't check to see if 'Test Two' exists for that student; so it would return results where a student has taken both 'Test One' and 'Test Two', but I want results where a student has taken 'Test One' with a grade of 'A' but hasn't taken 'Test Two' at all.
Click to expand...

OK, so maybe add a subquery to find the unique primary IDs that have a test two and filter those out

WHERE t.test_name = 'Test One'
and t.test_grade = 'A'
and s.primary_id not in (select distinct PRIMARY_ID from TESTS WHERE test_name = 'Test Two')
 
Rayn said:
and not exists (select t2.test_name where t2.test_name = 'Test Two' and t2.primary_id = t1.primary_id)
Click to expand...

this combined with Big Monkey's half of the query i'm presuming?

If so this is basically what I had before, which ran without syntax errors after i threw in the 'FROM' clause.. it just was taking forever, so I wasn't sure if it was correct.. given I've never done 'NOT EXISTS' sub-queries..

but i'll run with this and see where it ends up.
 
Big Monkey said:
OK, so maybe add a subquery to find the unique primary IDs that have a test two and filter those out

WHERE t.test_name = 'Test One'
and t.test_grade = 'A'
and s.primary_id not in (select distinct PRIMARY_ID from TESTS WHERE test_name = 'Test Two')
Click to expand...

I'll try this as well.. thanks
 
not exists is kind of slow. you could left outer join and it would be a bit faster
left outer join tests t2 on t1.primary_id = t2_primary_id and t2.test_name = 'Test Two'
WHERE t2.test_name IS NULL
 
^ also works, but you'd have to join twice to Tests if you use that syntax and still want to find those with grades of A only
 
Rayn just about had it there for you. If I am reading the requirements right, I believe you are asking for records for students that took test #1 and got an A but have not taken test #2 at all yet. I do my SQL in MS SQL2005 so hopefully this answer below is ANSI-92 compliant for you

Code: 
SELECT s.student_name
            ,s.student_age
            ,s.student_gradyear
            , t.test_name
            , t.test_grade
FROM STUDENT AS s
     INNER JOIN TESTS AS t ON (s.primary_id = t.primary_id)
WHERE t.test_name = 'Test One'
     AND t.test_score = 'A'
     AND NOT EXISTS (
                     SELECT t2.primary_id 
                     FROM TESTS AS t2 
                     WHERE t2.test_name = 'Test Two'
                     AND t2.primary_id = t.primary_id
                    )
  ORDER BY s.student_name;
 
Rayn said:
not exists is kind of slow. you could left outer join and it would be a bit faster
left outer join tests t2 on t1.primary_id = t2_primary_id and t2.test_name = 'Test Two'
WHERE t2.test_name IS NULL
Click to expand...

This is your best solution right here. Just add your other criteria of t1.test_name = 'Test One' AND t1.test_score 'A', then your set
 
You must log in or register to reply here.
Back
Top