Problem with some SQL queries

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Vince
    #1

    Problem with some SQL queries

    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.forename s, sessions.stage,
    sessions.course ,
    (select distinct title from shared.courses
    where sessions.course =shared.courses .course) as titleofcourse
    from people, sessions
    where people.person=s essions.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.su rname " 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 "
    Tags: None
    • Hans Forbrich
      #2
      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.forename s, sessions.stage,
      sessions.course ,
      (select distinct title from shared.courses
      where sessions.course =shared.courses .course) as titleofcourse
      from people, sessions
      where people.person=s essions.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.su rname " 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

        Comment

        Previous template Next
        Working...