| re: Problem with some SQL queries
Vince wrote:[color=blue]
>
> 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?[/color]
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.
[color=blue]
>
> 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?
>[/color]
Lean about the CONCAT operator.
[color=blue]
> 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 "[/color]
Think about using a grouping-count and eliminate the non-duplicate rows
using a 'having' clause.
HTH
/Hans
| 
