Results 1 to 4 of 4

Thread: Self-Join in a table

  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Self-Join in a table

    Hi,

    I am quite new to SQL and working on self-joins but not able to get an answer to this query.

    In the table there 4 columns as below...

    Number Status Plan_INST
    123 AC GSM
    345 DP GPRS
    345 AC GSM

    AC = Active
    DP = Deactivate Permanently

    The table contains thousands of "Numbers". And I want a list of those whose "Status" is AC and have got only GSM, and not GPRS. So either status of GPRS should be DP or it's not associated with the Number.

    Thanks in advance.
    Kapil...

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Not clear on what you want to get,

    select * from
    table
    where status='AC'
    and plan_inst='GSM'

  3. #3
    Join Date
    Dec 2010
    Posts
    2
    hi Skhanal,

    this query will give me also those Numbers whose GPRS is active.

    The condition for the Numbers is as below...
    1. Should have GSM installed.
    2. GPRS should be deactivated or is not installed (i.e., NULL)

    The query won't show me the GPRS row but it might be active for a particular number, which is not the criteria.

    Thanks

  4. #4
    Join Date
    Feb 2011
    Posts
    3

    Smile Re : Self-Join in a table

    Hi kaps,


    SELECT * FROM
    table WHERE plan_inst='GSM' AND (status = 'DP' OR status IS NULL )

    this query will return those rows which have GSM istalled and GPRS as not installed (i.e NULL ) or DP.

    ELSE this will also work


    SELECT * FROM
    table WHERE plan_inst='GSM' AND status <> 'DP'

Posting Permissions

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