SQL GroupBy on 2 different tables

Trying to come up with a query which shows that a particular name did not order particular item

Here I'm looking to get the Name which did not order Item 1 (or 2 or 3)

When I join the 2 tables, and do where itemid != 1, I'm still getting Order 1 rows with Item 2,3

How do I get only Order 4 when I do itemid != 1?

Here I'm looking to get the Name which did not order Item 1 (or 2 or 3)

When I join the 2 tables, and do where itemid != 1, I'm still getting Order 1 rows with Item 2,3

How do I get only Order 4 when I do itemid != 1?

How about...

Select Name
from t1
where not exists (
  select 1 from t2
  where t2.OrderId = t1.OrderId 
     and t2.ItemId = 'item 1'
);

Assume you only want to use Basic SQL syntax, you can use JOIN, GROUP BY, AND 2 conditions(haven't ordered item1 AND have ordered other items) in HAVING clause to get what you want.

See the following code as example(get the name who haven't order item 1:

SELECT
   o.name
FROM
    orders o
JOIN
    order_items oi
ON
    o.order_id = oi.order_id
GROUP BY
    o.name
HAVING
    (SUM(CASE WHEN oi.item_id =1 THEN 1 ELSE 0 END) =0
        AND
    SUM(CASE WHEN oi.item_id !=1 THEN 1 ELSE 0 END) >0

) 

RUNNING EXAMPLE SQLFIDDLE LINK

example output:

Or like this - a failing LEFT JOIN - using the data formats as from your dbfiddle entry...

WITH
-- your input
orders("Name",Order_ID) AS (
          SELECT 'Name 1','Order 1'
UNION ALL SELECT 'Name 2','Order 2'
UNION ALL SELECT 'Name 3','Order 3'
UNION ALL SELECT 'Name 4','Order 4'
)
,
order_items(Order_ID,Item_ID) AS (
          SELECT 'Order 1',1
UNION ALL SELECT 'Order 1',2
UNION ALL SELECT 'Order 1',3
UNION ALL SELECT 'Order 2',1
UNION ALL SELECT 'Order 2',2
UNION ALL SELECT 'Order 2',3
UNION ALL SELECT 'Order 2',4
UNION ALL SELECT 'Order 2',5
UNION ALL SELECT 'Order 3',1
UNION ALL SELECT 'Order 3',2
UNION ALL SELECT 'Order 3',4
UNION ALL SELECT 'Order 4',3
UNION ALL SELECT 'Order 4',4
UNION ALL SELECT 'Order 4',5
UNION ALL SELECT 'Order 4',6
)
-- end of your input - real query starts below
SELECT
  "Name"
FROM orders
LEFT JOIN order_items
  ON order_items.order_id=orders.order_id
 AND item_id = 1
WHERE order_items.order_id IS NULL;
-- out   Name  
-- out --------
-- out  Name 4

It's a little difficult to reply not having the actual tables at hand and not knowing the exact structure of the output you wish to see.

This query basically returns you the names of the people that DID NOT order some item, and you choose which one by picking the given_item_id. If you need more granular output, like some item on the first row and then the list of the people who did not order it on the left then we might need to know the DB you are using (Oracle? PostgreSQL? MySQL?), as this information is required to pick the correct aggregation/concatenation function.

SELECT DISTINCT ords.person_name
FROM orders AS ords
LEFT JOIN items AS itms ON ords.order_id = itms.order_id
WHERE itms.item_id = 'given_item_id'
   OR ords.person_name NOT IN (
     SELECT person_name
     FROM orders
     WHERE order_id IN (
       SELECT order_id
       FROM items
       WHERE item_id = 'given_item_id'
     )
   );

An other way to do it using left join

select t1.Name
from table1 t1
left join (
select Name
from table1 t1
inner join table2 t2 on t1.OrderID = t2.OrderID
WHERE t2.ItemID = 'Item 1'
) as s on s.Name = t1.Name
where s.Name is null;

Demo here

Jast another example, all not ordered by Name items

select n.Name,i.ItemId,t.ItemId xItemId from
table1 n left join (select distinct  ItemId from table2) i on 1=1
left join
(
select t1.Name,t2.ItemId
from table1 t1 left join table2 t2 on t2.OrderId=t1.OrderId
group by t1.Name,t2.ItemId
)t on t.Name=n.Name and t.ItemId=i.ItemId
where t.Itemid is null