Re: Problem with some SQL queries

From: Vince <vince_at_nospam.net.invalid>
Date: Wed, 18 Feb 2004 22:55:52 +0000
Message-ID: <4033ed7a$0$28145$636a15ce@news.free.fr>

Douglas,

Thanks a lot for your help. I have other problems (at the end of the post)

> First off, the WHERE clause of "title IS NOT NULL OR title IS NULL" is
> redundant because it always evaluates to true (i.e. a tautology).

Indeed, it was stupid to add this condition.

> SELECT
> COUNT( DISTINCT title ) + SIGN( COUNT(*) - COUNT(title) )
> FROM
> people;
>
> The expression, COUNT(*), will return the number of rows. The expression,
> COUNT(title), will return the number of rows that have non-NULL values of
> title. SIGN will return 1 if the difference between these expressions > 0
> otherwise 0. (-1 is not an issue here).

The SIGN function is relly interesting. Thanks you.

> 2) To enclose the result of an expression in apostophes ('), I suggest that
> you use the || operator as follows:
>
> '''' || (select distinct title from shared.courses where
> sessions.course=shared.courses.course) || '''' as titleofcourse

Again, it is perfect. I didn't think to use both || and apostophes, but 4 apostophes and not 3 as I tried.

> 3) My suggestion is:
>
> SELECT
> surname, forenames, COUNT(*)
> FROM people
> GROUP BY surname, fornames
> HAVING COUNT(*) > 1
> ;

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

Other question:

Furthermore, could you please aloso check this problem, it would be very nice:

My query is:

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 get:

FORENAMES SURNAME SEQ EXTRAROLE

Chris        Borg      0    Director
Alex         Stel      1    Director
Hug          Hann      1    LinkMan

But, I don't need the SEQ column. I only want:

FORENAMES SURNAME EXTRAROLE

Chris        Borg      Director
Alex         Stel      Director
Hug          Hann      LinkMan

However, "order by teachers.seq" seems to need that I also select "teachers.seq". That's why I make a select into the previous select, as:

select distinct forenames, surname, extrarole from
(
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 'LinkMan'
when (teachers.type = 'S' and teachers.type <> 'DL' ) then 'Super'

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%') order by teachers.seq
)

Is it possible to make the same stuff with a single select statement?

Thanks a lot by advance.

Vince Received on Wed Feb 18 2004 - 16:55:52 CST