$15
Get all questions that are not in the community pot table

Very strange.

I have 2 tables:

question

community_pot

I want to see if there are any questions that need to be put into the community_pot. I was assuming I could get their ids with something simple like this:

select id from question where id not in (select question_id from payment_for_community_pot);

I get "empty set" but I know there are hundreds of questions that are not in the community_pot.

What I'm actually looking for are those questions that get marked with a status of 'community_pot' but which have not yet had their ids recorded in the community_pot table.

I tried:

 select sum(question.prize_amount) as totalOwed from question  where question.status = 'community_pot' and question.id not in (select question_id from payment_for_community_pot);

+-----------+

| totalOwed |

+-----------+

|      NULL |

+-----------+

1 row in set (0.00 sec)

and:

select sum(question.prize_amount) as totalOwed from question left join payment_for_community_pot on question.id = payment_for_community_pot.question_id where question.status = 'community_pot' and question.id not in (select payment_for_community_pot.question_id from payment_for_community_pot);

+-----------+

| totalOwed |

+-----------+

|      NULL |

+-----------+

1 row in set (0.01 sec)

I would think this is easy but for some reason I can not get it right.

My 2 tables are below. What is the query I'm looking for?

 explain payment_for_community_pot;

+-----------------------+--------------+------+-----+---------+----------------+

| Field                 | Type         | Null | Key | Default | Extra          |

+-----------------------+--------------+------+-----+---------+----------------+

| id                    | int(11)      | NO   | PRI | NULL    | auto_increment |

| user_id               | int(11)      | NO   |     | NULL    |                |

| question_id           | int(11)      | YES  |     | NULL    |                |

| date                  | datetime     | NO   |     | NULL    |                |

| amount                | float        | YES  |     | NULL    |                |

| type                  | varchar(255) | YES  |     | NULL    |                |

| paypal_transaction_id | varchar(255) | NO   |     | NULL    |                |

+-----------------------+--------------+------+-----+---------+----------------+

7 rows in set (0.00 sec)

and:

 explain question;

+-----------------------------------------------+--------------+------+-----+---------+----------------+

| Field                                         | Type         | Null | Key | Default | Extra          |

+-----------------------------------------------+--------------+------+-----+---------+----------------+

| id                                            | int(11)      | NO   | PRI | NULL    | auto_increment |

| user_id                                       | int(11)      | NO   | MUL | NULL    |                |

| status                                        | varchar(255) | NO   |     |         |                |

| subject                                       | varchar(255) | NO   |     |         |                |

| description                                   | text         | NO   |     | NULL    |                |

| prize_amount                                  | float        | YES  |     | NULL    |                |

| gross_payment_amount                          | float        | YES  |     | NULL    |                |

| is_private                                    | tinyint(4)   | YES  |     | 0       |                |

| start_at                                      | datetime     | YES  |     | NULL    |                |

| end_at                                        | datetime     | YES  |     | NULL    |                |

| image                                         | varchar(255) | NO   |     |         |                |

| created_at                                    | datetime     | YES  |     | NULL    |                |

| updated_at                                    | datetime     | YES  |     | NULL    |                |

| paypal_transaction_id                         | varchar(255) | NO   |     |         |                |

| pending_increase_in_prize                     | float        | NO   |     | NULL    |                |

| pending_increase_in_gross_payment_amount      | float        | NO   |     | NULL    |                |

| final_message                                 | text         | NO   |     | NULL    |                |

| landing_page                                  | varchar(255) | NO   |     |         |                |

| category                                      | varchar(255) | NO   |     |         |                |

| profit_from_this_question_has_been_calculated | varchar(1)   | NO   |     |         |                |

| amount_paid_to_affiliates                     | float        | NO   |     | NULL    |                |

| user_who_signed_up_this_user_has_been_paid    | char(1)      | NO   |     |         |                |

| amount_paid_to_users_who_signed_up_this_user  | float        | NO   |     | NULL    |                |

| refund_request_explanation                    | text         | NO   |     | NULL    |                |

| refund_requested_date                         | datetime     | NO   |     | NULL    |                |

| voting_starts                                 | datetime     | NO   |     | NULL    |                |

| refund_amount                                 | float        | NO   |     | NULL    |                |

+-----------------------------------------------+--------------+------+-----+---------+----------------+

27 rows in set (0.00 sec)

Lawrence Krubner | 10/22/11 at 1:04am | Edit

(2) Possible Answers Submitted...

Last edited:
10/22/11
4:29am
Russell Smith says:
This query should get a list of ids from the question table that have status 'community_pot' but aren't in the payment_for_community_pot table:

select
q.id
from
question as q
left outer join payment_for_community_pot as pfcp
on (q.id = pfcp.question_id)
where
q.status = 'community_pot'
and pfcp.id is null;

You could also use "not exists":

select
q.id
from
question as q
where
q.status = 'community_pot'
and not exists (select
1
from
payment_for_community_pot as pfcp
where
q.id = pfcp.question_id);

To get the total owed:

select
sum(q.price_amount) as totalOwed
from
question as q
left outer join payment_for_community_pot as pfcp
on (q.id = pfcp.question_id)
where
q.status = 'community_pot'
and pfcp.id is null;
- 10/22/11 4:35am
  
  Russell Smith says:
  Hmm, looking a little more closely at your schema I see that the amount columns are floats (?!) and allow NULLs. You should probably use decimal/numeric columns (exact values as opposed to approximates), and have the columns default to 0.
  
  For example, change:
  ...
  prize_amount float,
  ...
  
  To:
  ...
  prize_amount decimal(8, 2) not null default 0
  ...
  
  If you add "NULL" (which means 'unknown') to something, the result is typically NULL. Because of this, this query to get the outstanding prize amounts instead:
  
  select
  sum(coalesce(q.price_amount, 0.0)) as totalOwed
  from
  question as q
  left outer join payment_for_community_pot as pfcp
  on (q.id = pfcp.question_id)
  where
  q.status = 'community_pot'
  and pfcp.id is null;
Last edited:
10/23/11
9:09am
Bill Hunt says:
It looks like you're allowing nulls in your amount columns - if you've got even one null in that column, it will sum to NULL rather than 0 which one would expect. Adding a where IS NOT NULL on the column should fix it.