CODEWITHSUNDEEP
sqlsql-server
unitario
unitario

Reputation: 6535

Filter a table with column values represented as rows

I do not know how to phrase this question so it makes sense but the problem is probably best understood through the example below.

My table structured in such a way that an ID can have different row values:

PK   ID        VALUE
1    160487    10122
2    160487    MF
3    166980    10147
4    166980    MF
5    166986    10147
6    166986    MF
7    166695    10121

I need to select a list of the numeric values and corresponding ID number for every ID that have the value "MF" attributed to it:

PK   ID        VALUE
1    160487    10122
3    166980    10147
5    166986    10147

How do I approach this problem? I use SQL Server 2005.

Upvotes: 0

Views: 60

Answers (5)

Benjamin RD
Benjamin RD

Reputation: 12034

You can use: SELECT * FROM tableName WHERE ISNUMERIC(VALUE) = 1 AND ID IN (SELE ID FROM tablewhere value = 'mf')

Upvotes: 0

Bohemian
Bohemian

Reputation: 425198

The most efficient way is to join the table to itself:

select t1.*
from mytable t1
join mytable t2 on t2.id = t1.id
    and t2.value = 'mf'
where isnumeric(t1.value)

If the value column only contains *either^ numeric values or 'mf', you could improve performance by changing the where clause to:

where t1.value != 'mf'

Upvotes: 0

Vishal Patel
Vishal Patel

Reputation: 973

Try this...

SELECT * FROM tableName WHERE id IN (
SELECT DISTINCT dt.id FROM tableName dt WHERE ISNUMERIC(dt.value) = 0)
AND ISNUMERIC(value) = 1

Upvotes: 0

sgeddes
sgeddes

Reputation: 62851

Here is one way with IN:

select *
from yourtable
where isnumeric(value) = 1
  and id in (select id from yourtable where value = 'mf')

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460208

If you always have two records where one has MF as value and the other is numeric you can use:

SELECT PK, ID, VALUE
FROM dbo.TableName t
WHERE EXISTS(SELECT 1 FROM dbo.TableName t2
             WHERE t2.ID=t.ID AND t2.VALUE='MF')
AND ISNUMERIC(t.VALUE) = 1

Sql-Fiddle demo

If you have multiple records with the same ID and with numeric values and you want to see only one you need to GROUP BY ID or use ROW_NUMBER in a CTE. However, if that's not the case above is easier.

Upvotes: 2

Related Questions