Re: Problem with some SQL queries

Vince,

> Excellent! I would like to improve this query by adding 2 id columns as
> following:
>
> SURNAME FORENAMES PERSON1 PERSON2
> McTa Paul 962 908
>
> where I have in fact 2 id (=preson parameter) for the same person in my
> people table (defined as "PERSON SURNAME FORENAMES TITLE KNOWNAS
> USERNAME EMAIL") and for which a simple query give for example:
>
> SURNAME FORENAMES PERSON
> McTa Paul 962
> McTa Paul 908

One suggestion would be:
SELECT surname, forenames, person1 AS person FROM people UNION
SELECT surname, forenames, person2 AS person FROM people ;

Your requirement can be restated as the union of two projections (aka SELECT lists) on the people table. The first projection uses column PERSON1 which is renamed to PERSON. The second projection uses PERSON2 in a similar way. Once the requirement has been rewritten in a relational way, the query follows naturally.

> select distinct people.forenames, people.surname, teachers.seq,
> case
> when (teachers.type = 'DL' and teachers.type <> 'D' and teachers.type
> <> 'L' ) then 'Director'
> when (teachers.type = 'D' and teachers.type <> 'DL' ) then 'Director'
> when (teachers.type = 'L' and teachers.type <> 'DL' ) then 'Lecturer'
> when (teachers.type = 'S' and teachers.type <> 'DL' ) then 'Supervisor'
> end as extrarole
> from teachers, people
> where teachers.teacher=people.person and (teachers.type like '%D%' or
> teachers.type like '%L%' or teachers.type like '%S%')
> and teachers.unit='COMS30103'
> order by teachers.seq

I think you need to improve your understanding of boolean expressions. The expression "(teachers.type = 'DL' and teachers.type <> 'D' and teachers.type <> 'L' )" has the same truth value as ""(teachers.type = 'DL' )" because if the value of the column teachers.type is equal to 'DL', then it must follow that the said value is not equal to either of 'D' or 'L'. The same comment applies to the other three (3) boolean expressions in your CASE expression.

> However, "order by teachers.seq" seems to need that I also select
> "teachers.seq".

This is not true for Oracle. Just remove the column seq from the SELECT list.

Douglas Hawthorne Received on Wed Feb 18 2004 - 18:58:06 CST