exclude recordset results from another recordset

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
vcarter
Forum Newbie
Posts: 14
Joined: Sun Jun 21, 2009 3:02 pm

exclude recordset results from another recordset

Post by vcarter »

I am trying to exclude the results of one recordset from a second recordset.

1st recordset pulls all categories from a table that match a specific criteria.
2nd recordset pulls all categories from the same table, but if these categories have appeared in the 1st recordset, I don't want them returned.

I'm not having any luck figuring out how to set this up. Any help would be greatly appreciated.

Thanks in advance!
patrickmvi
Forum Commoner
Posts: 32
Joined: Mon Jun 22, 2009 6:45 am
Location: Fort Lauderdale, FL

Re: exclude recordset results from another recordset

Post by patrickmvi »

A LEFT JOIN might be what you're looking to do here. What you would do is write your query for the second dataset and then as part of that query, you could do a LEFT JOIN on itself which would cause the joined portion to not return based on your criteria for the first query, then all you'd need to do is add a where clause that would check is the joined data is NULL or not. If you provide more information on the tables and fields used, I can provide an example.
vcarter
Forum Newbie
Posts: 14
Joined: Sun Jun 21, 2009 3:02 pm

Re: exclude recordset results from another recordset

Post by vcarter »

Thank you for the response. I'll try to put information here to make it a bit more clear.

There are three tables:
categories (this contains a list of categories that could be attached to any given item - there are about 270 categories)
items (this table contains all of the items)
item_category (this table contains the categories attached to specific items)

What I'm trying to do involves the admin pages. This is a page that shows the following:

1. The current categories attached to a specific item
2. All the rest of the categories

On this page, the user has the option of deleting current categories from an item, OR adding additional categories to an item. (I don't need help on this part.)

So, I have two recordsets. The first one is this:

Code: Select all

 
$query_rsCategory = sprintf("SELECT categories.category_name, item_category.category_id FROM item_category INNER JOIN categories ON item_category.category_id=categories.category_id
WHERE item_id = %s
ORDER BY category_name", GetSQLValueString($colname_rsCategory, "int"));
 
The second is this:

Code: Select all

 
$query_rsAllCategory = sprintf("SELECT DISTINCT categories.category_name, item_category.category_id FROM item_category INNER JOIN categories ON item_category.category_id=categories.category_id
WHERE item_id != %s
ORDER BY category_name", GetSQLValueString($colname_rsAllCategory, "int"));
 
I need the second recordset to give me ALL CATEGORIES minus the ones that are returned in the recordset above. I thought I had it -- but what's happening is that if there is a category that has NO ITEM attached to it (or vice versa), then it's not being returned at all, and I need it to show all of them.

Does that make sense? Thanks for your help. :)
Last edited by Weirdan on Tue Jun 23, 2009 4:27 pm, edited 1 time in total.
Reason: added [code=php] tags
patrickmvi
Forum Commoner
Posts: 32
Joined: Mon Jun 22, 2009 6:45 am
Location: Fort Lauderdale, FL

Re: exclude recordset results from another recordset

Post by patrickmvi »

I think this might be what you're looking for:

Code: Select all

 
SELECT DISTINCT categories.category_name, item_category.category_id FROM categories LEFT JOIN item_category ON categories.category_id=item_category.category_id WHERE item_category.category_id IS NULL ORDER BY categories.category_name
 
Hope that helps.
Last edited by Benjamin on Tue Jun 23, 2009 11:09 pm, edited 1 time in total.
Reason: Added [code=sql] tags.
Post Reply