CODEWITHSUNDEEP
sqldatabasesql-server-2008
Bima Ardi
Bima Ardi

Reputation: 81

how to query some values that different based on another value

I would like to know how can I make query to return values that have another different "value" based on, hmm i don't know what it should be called, identifier? For example, I have data like :

id    | date       | user | item  |      material      | Text | Description |
------+------------+------+-------+--------------------+------+-------------+
12345 | 31.03.2015 | ID00 | 00010 | 000000001011000106 | abcd |  something  | 
12345 | 31.03.2015 | ID00 | 00020 | 000000001011010105 | abcd |  test       |
12345 | 31.03.2015 | ID00 | 00030 | 000000001011020106 | abcd |  something  |
12345 | 31.03.2015 | ID00 | 00040 | 000000001011020104 | abcd |  something  |
44455 | 31.03.2015 | ID00 | 00010 | 000000001011040106 | abcd |  something  |
44455 | 31.03.2015 | ID00 | 00020 | 000000001011000105 | abcd |  something  |
44455 | 31.03.2015 | ID00 | 00030 | 000000001011010104 | abcd |  something  |
67890 | 31.03.2015 | ID00 | 00010 | 000000001011100402 | abcd |  something  |
77777 | 31.03.2015 | ID00 | 00010 | 000000001011100402 | bcad |  something  |
80808 | 31.03.2015 | ID00 | 00010 | 000000001011140102 | abcd |  something  |
90009 | 31.03.2015 | ID00 | 00010 | 000000001011100102 | dcba |  something  |
66666 | 01.04.2015 | ID00 | 00010 | 000000001011160101 | cdab |  somesome   |

And, I want to get result based on id and description only. Let say, all id '44455' have value for description which is 'something'. It shouldn't be displayed. But for id '12345' all the description is 'something' except 1 row that contain different value which is, 'test'

So, the rows that should be displayed are description that contains value null, and rows that contains different values description based on identifier id. From the example above, it should be printed:

id    | date       | user | item  |      material      | Text | Description |
------+------------+------+-------+--------------------+------+-------------+
12345 | 31.03.2015 | ID00 | 00010 | 000000001011000106 | abcd |  something  | 
12345 | 31.03.2015 | ID00 | 00020 | 000000001011010105 | abcd |  test       |
12345 | 31.03.2015 | ID00 | 00030 | 000000001011020106 | abcd |  something  |
12345 | 31.03.2015 | ID00 | 00040 | 000000001011020104 | abcd |  something  |

How can I make a condition in query for this case? Any advise?

Thanks in advance

Upvotes: 0

Views: 43

Answers (2)

Naresh Pansuriya
Naresh Pansuriya

Reputation: 2045

can you try this query

select *
from tbl a
where 1 < (select count(*) from tbl b where a.id = b.id group by b.description);

Upvotes: 0

Iswanto San
Iswanto San

Reputation: 18569

You can query using EXISTS.

Assuming your table name is tbl, then the query is:

select *
from tbl a
where exists (select 1 from tbl b where a.id = b.id and b.description <> 'Something');

Upvotes: 2

Related Questions