CODEWITHSUNDEEP
mysqlsqljoinmariadb
Azam
Azam

Reputation: 23

join two table which dont exist in another and exist with some condition

i have two table, table items and cart.

table items

| id     | name |
|--------|------|
| item_1 | A    |
| item_2 | B    |
| item_3 | C    |
| item_4 | D    |
| item_5 | E    |

table cart

| id | item_id | status |
|----|---------|--------|
| 1  | item_1  | 0      |
| 2  | item_1  | 1      |
| 3  | item_2  | 1      |
| 4  | item_2  | 0      |
| 5  | item_3  | 0      |
| 6  | item_4  | 1      |

how do I get items that are not related to carts and items that are last related but whose status is 0?

i tried some query

SELECT * 
FROM `items` t1 
JOIN cart t2 ON t1.id = t2.item_id 
WHERE t2.id IN (SELECT MAX(cart.id) FROM cart GROUP BY (cart.item_id)) 
AND t2.status = 0

but the result is

| id     | name |
|--------|------|
| item_2 | B    |
| item_3 | C    |

the expected result

| id     | name |
|--------|------|
| item_2 | B    |
| item_3 | C    |
| item_5 | E    |

where is item_5 are not related with cart and item_2 whose last related but status is 0

Any help is much appreciated! thanks in advance

Upvotes: 0

Views: 60

Answers (4)

Catalina Chircu
Catalina Chircu

Reputation: 1572

First of all your JOIN will eliminate the elements which exist in cart and not in items. I suggest, for example : 

SELECT * 
FROM items t1 
WHERE t1.id NOT IN (SELECT DISTINCT cart.item_id FROM cart)

UNION

SELECT DISTINCT t2.item_id, t1.name 
FROM items as t1 
JOIN cart t2 on t1.id=t2.item_id
WHERE t2.id in 
(SELECT MAX(cart.id) FROM cart GROUP BY cart.item_id)
and t2.status=0;

Upvotes: 0

forpas
forpas

Reputation: 164089

You can do it with a left join of items to a query that returns the last row of each item_id in the table cart:

select i.id, i.name
from items i left join(
  select c.* from cart c
  where not exists (
    select 1 from cart 
    where item_id = c.item_id and id > c.id 
  )  
) c
on c.item_id = i.id
where c.item_id is null or c.status = 0

See the demo.
Results:

| id     | name |
| ------ | ---- |
| item_2 | B    |
| item_3 | C    |
| item_5 | E    |

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Add a condition for non-existent records:

SELECT i.* 
FROM `items` i  
WHERE (c.id = (SELECT MAX(c2.id) FROM cart c2 WHERE c2.item_id = c.item_id) AND
       c.status = 0
      ) OR
      NOT EXISTS (SELECT 1 FROM cart c2 WHERE c2.item_id = c.item_id)

Upvotes: 0

Pepper
Pepper

Reputation: 587

Reposting my comment on a deleted answer:

SELECT t1.* 
FROM `items` t1 
LEFT JOIN `cart` t2 ON t1.id = t2.item_id 
 AND t2.id IN (SELECT MAX(c.id) FROM `cart` c GROUP BY (c.item_id)) 
 AND t2.status = 1
WHERE t2.status IS NULL

The query above should do the trick, it takes all items of your items table which do not have 1 as last status in your cart table (so any with a 0 as last status, or not present at all in the cart)

Upvotes: 2

Related Questions