Re: Problem with some SQL queries

Vince wrote:
>
> 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?

Nulls have no defined value - not even 'nothing'. Therefore they can not be counted. To work around this, assign a value to replace the null using a function like NVL (although you really want to look it up to make sure you are using the right function) OR count a non-null column or pseudo-column such as rowid.

>
> 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?
>

Lean about the CONCAT operator.

> 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 "

Think about using a grouping-count and eliminate the non-duplicate rows using a 'having' clause.

HTH
/Hans Received on Wed Feb 18 2004 - 17:35:14 CET