CODEWITHSUNDEEP
sqlcompare
user1202994
user1202994

Reputation: 3

SQL on how to simultaneously compare two column values in a query

I need create a query to find the names of drivers whose exam scores get lower when they take more exams. So I have the following tables:

branch(branch_id, branch_name, branch_addr, branch_city, branch_phone);
driver(driver_ssn, driver_name, driver_addr, driver_city, driver_birthdate, driver_phone);
license(license_no, driver_ssn, license_type, license_class, license_expiry, issue_date, branch_id);
exam(driver_ssn, branch_id, exam_date, exam_type, exam_score);

**the exam_date is a date

So I am using the tables driver and exam. I would like to somehow check that the exam_date>anotherDate while at the same time checking that exam_score

*EDIT

This is what I came up with but I feel like some of the syntax is illegal. I keep getting a syntax error.

s.executeQuery("SELECT driver_name " +
"FROM driver " +
"WHERE driver.driver_ssn IN " +
"(SELECT e1.driver_ssn" +
"FROM exam e1" +
"WHERE e1.exam_score < " +
"(SELECT e2.exam_score FROM exam e2)" +
"AND e1.exam_date > " +
"(SELECT e2.exam_date FROM exam e2)");

EDIT! I got it working! Thanks for your input everyone!

SELECT driver.driver_name 
FROM driver
WHERE driver.driver_ssn IN
    (SELECT e1.driver_ssn 
    FROM exam e1, exam e2, driver d 
    WHERE e1.exam_score < e2.exam_score
    AND e1.exam_date > e2.exam_date 
    AND e1.driver_ssn=e2.driver_ssn)

Upvotes: 0

Views: 43861

Answers (3)

topchef
topchef

Reputation: 19793

Simple take on this problem would be getting drivers who take a couple of exams and their second score is lower.

To compare columns from the same table SQL uses self-join. Your join condition should include:

select e1.driver_ssn, e1.exam_type, e1.exam_score as score_before, 
       e2.exam_score as score_after
exam e1 join exam e2 on (e1.driver_ssn = e2.driver_ssn and 
                    e1.exam_type = e2.exam_type and
                    e1.exam_date < e2.exam_date and 
                    e1.exam_score > e2.exam_score)

Upvotes: 0

PeteGO
PeteGO

Reputation: 5791

You will need to do a self join. See this example and work it to your schema.

select d.name, 
       es.date_taken as 'prev date', 
       es.score as 'prev score',
       es.date_taken as 'new date', 
       es_newer.score as 'new score'
  from driver d
         inner join exam_score es
           on d.id = es.driver_id
         left outer join exam_score es_newer
           on d.id = es_newer.driver_id 
          and es_newer.date_taken > es.date_taken
          and es_newer.score < es.score
 where es_newer.id is not null

Here is a SQL Fiddle that I made to demonstrate.

Upvotes: 1

Himanshu
Himanshu

Reputation: 2454

SELECT returns a set and you cannot compare a single value against a set. You can try something along these lines. This is similar to yours and doesn't handle three exam case :-

SELECT driver_name 
  FROM driver
  JOIN exam e1 ON driver_ssn
  JOIN exam e2 ON driver_ssn
 WHERE e1.exam_score < e2.exam_score
   AND e1.exam_date > e2.exam_date

The query selects all pairs of exams taken by a driver in which the score is less and the date big

Upvotes: 0

Related Questions