Help with two SQL Queries

Question

Hi,

I’m having some difficulties with two queries. I can get some of the information but my problem lies in the some special conditions I just can’t seem to get right in the SQL statement.

The first query is in natural language defined:

I’m going to find all the candidates who has acquired two qualifications but during the same time period, but from different institutes

My Relational schema looks like this:

BOLD = primary key

ITALICS = foreign key

candidate = (candidateid, name, birth, mail)

qualification = (qualificationid, instituteid, candidateid, datestarted, datefinished, degreename, major)

institute = (instituteid, institutename, city)

The SQL I’ve tried to run is not very pretty (at this point) and not working either but it is:

select (candidate.firstname, candidate.lastname) 
from candidate, qualification, institute 
where (select count(qualification.candidateid) from qualification where qualification.candidateid = 2) 
and between qualification.datestarted and qualification.datefinished
and qualification.instituteid <> qualification.instituteid

I gives me an error and I’m pretty, almost certain that it is in the COUNT clause but I can’t seem to crack it to how to count the numbers of candidateid’s and set a condition to two (2) and then I’m pretty sure as well that there is a problem in my checking on the time period. They have to be in the same time period. And by the way, the datestarted and the datefinished is in the DATE format.

My second query I’m having problems with is in natural language formulated like this:

Find the names of the companies who have all made inquiries about the candidates but with an average age below 30 (for the candidates).

My relation schemas look like this:

candidate = (candidateid, name, birth, mail)

qualification = (qualificationid, instituteid, candidateid, datestarted, datefinished, degreename, major)

inquiry = (inquiryid,candidateid, companyid)

The query I’ve tried is this:

select (company.companyname) as company
from company, inquiry, candidates
where company.companyid = inquiry.companyid
and inquiry.candidateid = candidate.candidateid
and (select avg(candidate.birth < (right(curdate(),5<right(birth,5)) from candidate)))

The BIRTH tuble is also described as a DATE field.

I hope you can help me out whit these two queries and if there is any question about 'em, please say so and I'll try to clarify.

Mestika

Answer 1

With regard to your first question: you have a few issues. One is that you have to join qualifications twice, since you want to compare the values from one match with another. Another is that your date comparison has no value to compare. It should be x between y and z -- in your example. you have no x.

After you join twice, then you can modify your last line to say qual1.instid <> qual2.instid

More details if you want, but I think that might get you started. As an aside, I find the SQL join syntax easier to deal with, but to each his own. One issue it helps resolve is the gigantic cartesian join you seem to have, because you don't appear to have any join criteria.

Answer 2

SELECT  *
FROM    candidate c
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    qualification q1
        JOIN    qualification q2
        ON      q2.candidateid = q1.candidateid
                AND q2.datestarted = q1.datestarted
                AND q2.dateended = q1.dateended
                AND q2.instituteid <> q1.instituteid
        WHERE   q2.candidateid = c.candidateid
        )

Answer 3

you other issue is using cartesian JOINS (listing the tables in the FROM clause separated by commas) instead of explicit JOIN (INNER or LEFT or RIGHT). You may find Understanding SQL Joins a good read.

Answer 4

subquery q should get a list of all candidate ids that have at least one other candidate at a different institute finishing in the same time period. The subquery is joined to the candidate table to get the names.

SELECT(c.firstname, c.lastname) 
FROM candidate c JOIN 
  (SELECT q1.candidate_id FROM qualification q1 
    JOIN qualification q2 ON (q1.institute_id!=q2.institute_id) 
    WHERE q1.datefinished BETWEEN $somestartdate AND $someenddate 
      AND q2.datefinished BETWEEN $somestartdate AND $someenddate)q
ON (c.candidateid = q.candidateid)