Implement foreach logic in SQL

Question

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.

That's a very poorly normalized table! If this is from a real-life application you really should do something about it. — Gert Arnold
– Gert Arnold, Commented Dec 27, 2020 at 16:11
But apples can be red and green (and other colours). How would you know what property belongs to another? Your design needs fixing here. — Thom A
– Thom A ♦, Commented Dec 27, 2020 at 16:16
Apologizes. That's my first time to use Table here. It looked OK when I previewed it. But lost format after I posted this question. Thanks for the comment and edit. — Simon
– Simon, Commented Dec 27, 2020 at 16:17
@GertArnold isn't talking about how you presented the tables they are saying your design in your actual database is flawed. — Thom A
– Thom A ♦, Commented Dec 27, 2020 at 16:19
EAV strikes again! If you are designing your own database, then research that term to understand the consequences and the very real problems you will face trying to extract meaningful information from it. — SMor
– SMor, Commented Dec 27, 2020 at 16:22

Gordon Linoff · Accepted Answer · 2020-12-27 16:07:53Z

3

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

answered Dec 27, 2020 at 16:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Implement foreach logic in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related