Compare same field in same table 1

[leftfldr]

Using SQL2005 against Dynamics GP. I have a table that contains inventory items. We have inventory items that end with a +C and +O with the same preceeding characters. I need to generate a query that will give me a list of all +C items without correspoding +O items. How should I go about this.

Example data:
1234+C
1234+O
4567+C
9876+C
9786+O

Query should return:
4567+C

Any help or suggestions would be appreciated!

 

[markros]

SQL Server 2005 and up solution - not tested

select * from (select Field, count(*) over (partition by substring(Field, 1, LEN(Field)-2)) as CntUnique from myTable) X where CntUnique %2 = 1 -- Odd number of items (assuming we always have one with +C and one with +O - if we may have 2 +C and 1 +O the query would not work

 

[leftfldr]

OK, this makes sense, but I could we alter it to because we have items with no plus at all or just one or the other. So I am only concerned if it has a +C to start with.
Example:

111
1234
1234+C
1234+O
4567+C
678+O
9876+C
9786+O
999+C

Expected result:
4567+C
999+C

 

[markros]

you can add condition to the inner select

and Field like '%+%' to get only items including +. If this condition is not enough, then we can change it to
RIGHT(Field,2) IN ('+O','+C') -- the query would be non-sargable in this case but I don't see a simple way to make it sargable still and only consider last two chars.

 

[bborissov]

SELECT Table.*
FROM Table
LEFT JOIN Table Tbl1 ON Tbl1.TheField = REPLACE(TheField,'+C','+O')
WHERE RIGHT(Table.TheField,2) = '+C'

NOT TESTED!!!
Not optimizable also

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Boris, you forgot to add
and Tbl1.IDField IS NULL
to only get non-matched records.

 

[markros]

Ok, one extra non-tested solution

;with cte1 as (Select *, substring(Field,1,len(field)-2) as Test from myTable
where Field like '%+C'), cte2 as (Select *, substring(Field,1,len(field)-2) as Test from myTable
where Field like '%+O')

select cte1.* from cte1 where not exists(select 1 from cte2 where cte2.Test = cte1.test)
UNION ALL
select cte2.* from cte2 where not exists(select 1 from cte1 where cte2.Test = cte1.test)

 

[bborissov]

Sure, I forgot

SELECT Table.*
FROM Table
LEFT JOIN Table Tbl1 ON Tbl1.TheField = REPLACE(TheField,'+C','+O')
WHERE RIGHT(Table.TheField,2) = '+C' AND
      Tbl1.TheField IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Anyway, get a star

 

[leftfldr]

Thanks so much. The first solution did work for me and got me exactly what was needed!

 

[markros]

Which particular solution worked for you? There were a couple of them in the thread.

 

[leftfldr]

select * from (select Field, count(*) over (partition by substring(Field, 1, LEN(Field)-2)) as CntUnique from myTable) X where CntUnique %2 = 1

combined with
and Field like '%+%' to get only items including +.

This worked great!

 

[markros]

Ok, then you could have probably marked that solution?