Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Unanswered: A question on subquery return

    Here is subquery for a table, called pStatus

    pStatus
    userid
    status

    select 1 from pStatus where userid='Robert' and status='p'

    How to modify this query so that it also return true if there is't an entry for userid 'Robert'?

    Thanks,

    v

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: A question on subquery return

    How about:

    where (not exists (select 1 from pStatus where userid='Robert')
    or exists (select 1 from pStatus where userid='Robert' and status='p'))

  3. #3
    Join Date
    Oct 2002
    Posts
    37
    Thanks Tony.

    That is quick and sharp one.

    v.

  4. #4
    Join Date
    Oct 2002
    Posts
    37
    When I posted this question, I was thinking about using only one "select". The subquery is a small section of a long query with near 1,700 characters. And there are 10 similar subqueries in the query statement needed to have the same change. After running the planner, the performane doesn't seem too bad.

    Nested Loop (cost=1.16..33.58 rows=1 width=398) (actual time=2.00..2.00 rows=0 loops=1)

    ...
    Total runtime: 7.00 msec

    Only few data at this moment.

    v.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •