Fw: Problem with some SQL queries

From: Vince <vince_at_nospam.net.invalid>
Date: Wed, 18 Feb 2004 20:17:03 +0000
Message-ID: <4033c83e$0$28103$626a14ce@news.free.fr>

Fw from comp.databases.oracle

I have several questions about queries I make into my database. I will explain you. Could you please help me?

1)In my people table, persons have a title (Mr, Miss, or whatever).

" select distinct person, title from people where title is null or title is not null "

give:

PERSON TITLE
0000029 Miss
0000465 Mr

0000469        <-- null
0000624        <-- null

0000900 Miss

But when I calculate the number of titles (a lot of possibles types), the title = null is not took into account. For that, I use " select count(distinct title) from people where title is null or title is not null ". How to make it successful?

2)Parameter between ' characters

My query is:

select distinct people.surname, people.forenames, sessions.stage, sessions.course,
(select distinct title from shared.courses where sessions.course=shared.courses.course) as titleofcourse from people, sessions
where people.person=sessions.student and sessions.status='C'

I get:

SURNAME  FORENAMES           STAGE  COURSE  TITLEOFCOURSE
Aggett  Stephen Peter James  2      V700    Philosophy     <--

'Philosophy'

How to put the title of course (getting with a select) between ' characters?
I try without succes to use case or decode(). Have you got an idea?

3)A query on a same table

My people table is as following:

PERSON SURNAME FORENAMES TITLE KNOWNAS USERNAME EMAIL I would like to check if there is any duplicates, ie two people with the same surname and the same forenames.

I don't want to use a view containing the people table. I think it is better to make it in a single query. Any idea please?

Something like " select * from people as p, people as pp where p.surname=pp.surname " but which works?

Maybe it is something as:
"select p.surname, p.forenames
from people p, (select pp.surname, pp.forenames from people pp) where p.surname = pp.surname and p.forenames = pp.forenames " Received on Wed Feb 18 2004 - 14:17:03 CST