Need help for writing SQL query

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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 13-Jul-15 23:06pm

koli.pankaj

Add a Solution

3 solutions

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

as your result.

Posted 13-Jul-15 23:18pm

Shanish K

Updated 13-Jul-15 23:39pm

v4

Comments

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?

Posted 13-Jul-15 23:30pm

Andy Lanng

Updated 13-Jul-15 23:31pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-13T23:33:00

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