Page 1 of 1
More Query Join Troubles
Posted: Wed Jul 14, 2010 2:32 am
by JakeJ
I'm not quite getting the results I should from this query.
I have 3 tables:
categories: id, description
items: id, category_id, description
budget_items: id, client_id, category_id, items_id, note
My table should look like this:
item_description category_id item_id amount note client_id
Item 1 1 1 200 something 1000
Item 2 1 2 null null null
Item 3 1 3 null null null
So here's what I need. ALL items in category 1 and ANY entries from client_budgets where category_id = 1
I know what I need, I just can't seem to get the query to do it correctly. Thanks for the help.
Re: More Query Join Troubles
Posted: Wed Jul 14, 2010 2:48 am
by amargharat
Use following query
Code: Select all
SELECT bi.description as item_description, bi.category_id, i.id as item_id, bi.amount, bi.note, bi.client_id FROM bugget_items bi INNER JOIN items i ON bi.items_id = i.id
Re: More Query Join Troubles
Posted: Wed Jul 14, 2010 3:16 am
by JakeJ
amargharat wrote:Use following query
Code: Select all
SELECT bi.description as item_description, bi.category_id, i.id as item_id, bi.amount, bi.note, bi.client_id FROM bugget_items bi INNER JOIN items i ON bi.items_id = i.id
Ok. I get that bi is budget items, and I've made changes but I'm not sure what you're doing with this part:
FROM bugget_items bi INNER JOIN items i ON bi.items_id = i.id
Does that mean:
FROM budget_item budget_items INNER JOIN items items on budget_items_id = items.id??
I don't get why you had budget_items and items twice in the from statement. Please clarify.
Also, I need to restrict the categories to items.category_id = 1 and budget_items.client_id = 1000
But I still need ALL records from items matching category_id = 1 and only those records from budget_items where client_id = 1000.
Thank you for helping, I just need a little more clarification.
Re: More Query Join Troubles
Posted: Wed Jul 14, 2010 3:31 am
by JakeJ
Also, I tried the following:
*note, my original post had budget_items as a table. My mistakes, it's actual client_budgets.
When I try the following query, I get almost what I need.
SELECT items.id, items.description, items.category_id AS category, client_budgets.amount AS amount
FROM items
LEFT OUTER JOIN client_budgets ON items.id = client_budgets.item_id
WHERE items.category_id =5
... but it returns all client_id's and I need to restrict that so....
I do this:
SELECT items.id, items.description, items.category_id AS category, client_budgets.amount AS amount
FROM items
LEFT OUTER JOIN client_budgets ON items.id = client_budgets.item_id
WHERE items.category_id =1
AND client_budgets.client_id = '1000'
... now I'm back to getting a single row returned.
This is getting very frustrating. There has to be a way to get the data set I'm looking for.
Since the white space was taken out of the table in my original post, I'll do it better.
item description||category_id_||item_id||amount||note_____||client_id
Item 1 --------------1------------1----- 200-----something--- 1000
Item 2 --------------1----------- 2------null-----null-----------null
Item 3 --------------1----------- 3------null-----null-----------null
To reiterate...
I need all items from the items table where category = 1
And only those items from client_budgets where category = 1 and client_id = 1000
Re: More Query Join Troubles
Posted: Wed Jul 14, 2010 5:11 am
by VladSun
You should filter out clients in a LEFT JOIN condition and not "globally" int the WHERE clause:
Code: Select all
SELECT
items.id, items.description,
items.category_id AS category,
client_budgets.amount AS amount
FROM
items
LEFT OUTER JOIN
client_budgets ON
items.id = client_budgets.item_id
AND
client_budgets.client_id = '1000'
WHERE
items.category_id =1
And please, use [ syntax=sql] [/ syntax] (without spaces) to surround your SQL code.
Re: More Query Join Troubles
Posted: Wed Jul 14, 2010 5:26 am
by JakeJ
VladSun,
That worked perfectly. Thank you very much. I knew it had to be simple.