CODEWITHSUNDEEP
c#sqlsql-serverlinq
Simon
Simon

Reputation: 135

Implement foreach logic in SQL

I have a table Fruit like this:

Name Property Value
Apple Price $5
Apple Color Red
Apple Quantity 20
Pear Price $5
Pear Color Kelly
Pear Weight 50g
Pear Quality Good

Now I want to select all records of Pear that are different from Apple in either Property or Value (The result will be the last 3 rows)

In C# it will be

foreach(var apple in apples)
{
   var result = pears.All(pear => pear.Property != apple.Property && pear.Value != apple.Value)
}

In SQL Server I tried this but failed:

    Select * from Fruit where Name = 'Pear'
and exists
(select 1 from Fruit t where t.Name = 'Apple'
and t.Property <> Fruit.Property
and t.Value <> Fruit.Value)

How should I correct the SQL statement? Thanks.

Upvotes: 1

Views: 132

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use not exists:

select f.*
from fruit f
where f.name = 'Pear' and
      not exists (select 1
                  from fruit f2
                  where f2.name = 'Apple' and
                        f2.property = f.property and
                        f2.value = f.value
                 );

Upvotes: 3

Related Questions