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...

Not clear on what you want to get,

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

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

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':)