More Query Join Troubles

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

More Query Join Troubles

Post 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.
amargharat
Forum Commoner
Posts: 82
Joined: Wed Sep 16, 2009 2:43 am
Location: Mumbai, India
Contact:

Re: More Query Join Troubles

Post 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
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: More Query Join Troubles

Post 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.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: More Query Join Troubles

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: More Query Join Troubles

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: More Query Join Troubles

Post by JakeJ »

VladSun,

That worked perfectly. Thank you very much. I knew it had to be simple.
Post Reply