SQL query to select all tuples that do not have a specific attribute at all

I have a table (shown below) that maps users to the fruits that they like. This table is a result of a a complicated SELECT statement, and now I am left with specifying the WHERE condition.

I would like to select all users that do not like the fruit “Orange” at all.

U.id	F.fruit
1	Apple
1	Orange
1	Orange
2	Mango
3	Mango
4	Mango
4	Grapes
5	Apple
5	Orange
6	NULL

The resulting table should consist of users 2, 3, 4, 6, because users 1 and 5 have tuples that map them to “Oranges”.

I am not allowed to use any sub-queries (make another SELECT statement). I am not sure how to do this given these restrictions, and I was not sure of how to google search for SQL queries for this specific situation.

Answer

I would like to select all users that do not like the fruit “Orange” at all.

Assuming you have a table of users, I would suggest not exists:

select u.*
from users u
where not exists (select 1
                  from <your query here> q
                  where q.user_id = u.user_id and q.fruit = 'orange'
                 );

Alternatively, you could aggregate, but this would only return users in your query:

select user_id
from <your query here>
group by user_id
having count(*) filter (where fruit = 'orange') = 0;

Or, if you showed the query, there might be other solutions as well.