By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,196 Members | 973 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,196 IT Pros & Developers. It's quick & easy.

Problem with some SQL queries

P: n/a
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 "
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.