-
-
Save thequbit/6500121 to your computer and use it in GitHub Desktop.
[11:16:03] <blitz> ok so I have two tables, cards and shopping_carts | |
[11:16:51] <blitz> cards have a buy_cart_id that is a foreign key to a shopping cart, they also have a true/false flag called "offered_electronic" | |
[11:17:23] <blitz> I need to join all cards on shopping carts, but I need to reject every cart that includes any amount of cards where offered_electronic is set to 1 | |
[11:17:54] <blitz> so if atleast one card is set to 1, don't join any cards in that cart | |
create table shopping_carts( | |
cartid int not nul auto_increment primary key | |
); | |
create table cards( | |
cardid int not null auto_increment primary key, | |
buy_card_id int, | |
foreign key (buy_card_id) references shopping_carts(cartid), | |
offered_electronic bool not null | |
); | |
sudo code: | |
give me all cards in all of the carts that have all cards with offered_electronic = 0; | |
SQL: | |
SELECT shipping_carts.* FROM shopping_carts JOIN shopping_carts.cardid = cards.buy_card_id WHERE cards.offered_electonic = 0; |
yes, don't join any of those cards in a cart where 1+ cards are offered_electronic = 1
select sc.id
from shopping_carts sc
where not exists
(
select 1 from cards
where sc.id = cards.buy_cart_id
and cards.offered_electronic = 0
)
select sc.id
from shopping_carts sc
where not exists
(
select 1 from cards
where sc.id = cards.buy_cart_id
and cards.offered_electronic = 1
)
*** fixed.
SELECT
shopping_carts.,
cards.
FROM shopping_carts
JOIN cards
ON cards.buy_card_id = shopping_carts.cardid
WHERE cards.cardid NOT IN
(
SELECT cardid
FROM cards
WHERE offered_electronic = 1
)
This will select all of the cards that have offered_electronic = 1, and then return all of the shopping carts and cards that do not fall into that selected data. I believe this is what you want.
What we want:
If a cart has even one card with offered_electronic = 1, do not include that cart in the result.