15,790,022 members
See more:
LOCGUID Status
------------------
100 INA
100 INA
200 CUR
300 INA
300 CUR
400 INA

Above is my sample data.I want to write a query which will give me the all LOCGUID which has only Status INA.

Below is the sample output which I want

LOCGUID
----
100
400
Posted

## Solution 1

You can simply write

SELECT DISTINCT LOCGUID
FROM TABLE
WHERE STATUS = 'INA'

Sorry, I misunderstood with the column names.

If you execute the above query, you will get

100
300
400

v4
koli.pankaj 14-Jul-15 5:28am
no desired result
Shanish K 14-Jul-15 5:30am
Can you post your sql query, whice you have tried?

## Solution 2

OK - let's look at this step by step.

First, a very basic query to start us off:

SQL
```Select LOCGUID
from table```

this will return the list of LOCGUIDs (100,100,200,300,300,400)

we only want the items that have INA in the column Status so we can add a WHERE clause:

SQL
```Select LOCGUID
from table
where Status = 'INA'```

We now have a list that looks like: (100,100,300,400)

There are a couple of ways we can get rid of duplicates. For more complex expressions you can use GROUP BY clauses, but we can simply use the DISTINCT selector:

SQL
```Select distinct LOCGUID
from table
where Status = 'INA'```

Great. We have the unique LOCGUIDs where Status = 'INA' : (100,300,400)

Your list will look like this:

LOCGUID
----
100
300
400

Did you miss the '300' by mistake or is there another criteria that would eliminate it?

v2

## Solution 3

I take it LocGuid and Status are separate columns. If that is the case then simply
SQL
```SELECT LocGuid
FROM TableName
WHERE Status = 'INA'```

However this does not seem to match to your result example. In your example you have 100 only once and 300 not at all.
Based on that, dou you mean you want to have only such LocGuid values that contain no other status than INA. If this is what you're after then perhaos something like
SQL
```SELECT DISTINCT
t1.LocGuid
FROM Tablename t1
WHERE NOT EXISTS (SELECT 1
FROM TableName t2
WHERE t2.LocGuid = t1.LocGuid
AND   t2.Status <> 'INA')```

koli.pankaj 14-Jul-15 5:55am
It gives perfect result.
Thank you so much.
Wendelius 14-Jul-15 5:56am
You're welcome :)