SOLVED: Confusing Join Problem
Posted: Fri Jul 09, 2010 12:22 pm
**SOLVED** Scroll to bottom for solution.
I have 3 tables, categories, items, and budget_items. (Using MySQL)
items are assigned to a category.
budget_items contain client data and store the reference to the item_id and the category_id (among other things).
Here's what I have so far:
SELECT items.description AS item, client_budgets.amount AS amount, client_budgets.description AS notes, categories.description AS category
FROM items
LEFT JOIN client_budgets ON items.id = client_budgets.item_id
LEFT JOIN categories ON items.category_id = categories.id
WHERE client_budgets.client_id = '100'
AND categories.description = 'Gifts'
ORDER BY items.id
The above query works but there's a problem. Because I'm restricting by client_id, I only get back rows where client_budgets have a record with that client_id.
What I need is a data set that returns all items in the "Gifts" category whether or not there is a record in client_budgets with a matching entry.
So my table should look like this:
item amount notes category
Tithes 100 Tithe to 1st Baptist Gifts
Charitable Donations NULL NULL Gifts
Religious Donations NULL NULL Gifts
Other NULL NULL Gifts
When I restrict by client_id, I only get the first row in the table above.
I hope that all made sense.
**SOLUTION**
WHERE client_budgets.client_id = '100' should be AND client_budgets.client_id = '100'
I have 3 tables, categories, items, and budget_items. (Using MySQL)
items are assigned to a category.
budget_items contain client data and store the reference to the item_id and the category_id (among other things).
Here's what I have so far:
SELECT items.description AS item, client_budgets.amount AS amount, client_budgets.description AS notes, categories.description AS category
FROM items
LEFT JOIN client_budgets ON items.id = client_budgets.item_id
LEFT JOIN categories ON items.category_id = categories.id
WHERE client_budgets.client_id = '100'
AND categories.description = 'Gifts'
ORDER BY items.id
The above query works but there's a problem. Because I'm restricting by client_id, I only get back rows where client_budgets have a record with that client_id.
What I need is a data set that returns all items in the "Gifts" category whether or not there is a record in client_budgets with a matching entry.
So my table should look like this:
item amount notes category
Tithes 100 Tithe to 1st Baptist Gifts
Charitable Donations NULL NULL Gifts
Religious Donations NULL NULL Gifts
Other NULL NULL Gifts
When I restrict by client_id, I only get the first row in the table above.
I hope that all made sense.
**SOLUTION**
WHERE client_budgets.client_id = '100' should be AND client_budgets.client_id = '100'