Help with nested selects used to select unlike items

Ok so here is my problem....

I have one table that describes a bunch of buckets, which are all grouped by color.

Each of these buckets have a related table that holds an inventory of all of the items that are in those buckets.

I need to make a stored procedure that will be passed in the bucket color, and expect to get a bucket number back that is within that same color group but does not have any of the matching items of active buckets of that same color.

Bucket 

+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| BucketNum | int(11)    | NO   | PRI | NULL    |       |
| Group     | varchar(6) | NO   | PRI | NULL    |       |
| Full      | tinyint(1) | YES  |     | NULL    |       |
| Active    | tinyint(1) | YES  |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+


Bucket_Items
+------------------+------------+------+-----+---------+-------+
| Field            | Type       | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| Bucket_BucketNum | int(11)    | NO   | PRI | NULL    |       |
| Bucket_Group     | varchar(6) | NO   | PRI | NULL    |       |
| ItemNum          | varchar(6) | NO   | PRI | NULL    |       |
| Qty              | int(11)    | YES  |     | NULL    |       |
+------------------+------------+------+-----+---------+-------+







Bucket
(SELECT *)
+-----------+-------+------+--------+
| BucketNum | Group | Full | Active |
+-----------+-------+------+--------+
|         7 | BLUE  |    0 |      1 开发者_开发技巧|
|         8 | BLUE  |    0 |      0 |
|        12 | GREEN |    0 |      1 |
|        33 | GREEN |    0 |      0 |
|        56 | RED   |    0 |      0 |
|        99 | GREEN |    0 |      0 |
+-----------+-------+------+--------+


Bucket_Items
(SELECT *)
+------------------+--------------+---------+------+
| Bucket_BucketNum | Bucket_Group | ItemNum | Qty  |
+------------------+--------------+---------+------+
|                8 | BLUE         | AA11    |    1 |
|               12 | GREEN        | AA11    |   56 |
|               99 | GREEN        | AA11    |    7 |
|               12 | GREEN        | BB22    |    5 |
|                8 | BLUE         | CC33    |    3 |
|               12 | GREEN        | CC33    |    1 |
|               99 | GREEN        | DD44    |   88 |
|                7 | BLUE         | FF55    |   10 |
|               33 | GREEN        | FF55    |   56 |
|               33 | GREEN        | HH88    |   12 |
|               33 | GREEN        | JJ99    |   50 |
+------------------+--------------+---------+------+

so if I were to pass into the procedure 'GREEN'.

I want it to see that currently green has bucket 12 active in it's group, bucket 12 holds items AA11, BB22, & CC33 I would like the procedure to return 33.

33 passes because it is in green and has no shared items with bucket 12. Bucket 99 fails, because it shares the item AA11 with bucket 12 and therefore cannot be picked.

Sorry if this is a little confusing and I would be more than willing to help explain it more, I am really struggling and I am hoping that the community can help me here.

Thanks to you all in advance.

EDIT:

Sorry this is what I have so far, I just get a little lost with all of the nested selects:

DELIMITER // 
CREATE PROCEDURE example (IN group_color INT)
BEGIN 

SELECT * FROM SELECT DISTINCT bucket, itemnum FROM bucket_items i, bucket b  
WHERE b.group = group_color AND b.bucketnum = i.bucket_bucketnum) AS x 
WHERE itemnum != (SELECT item FROM bucket WHERE active = true AND Group=group_color) AS y;

END //
DELIMITER ;

Did not mean to come off as the "have the internet do the work for me" type

I won't write the code for you, but here's the idea: select buckets where the intersection of the items in the bucket and items in buckets of the given color is empty.

Keywords: select, where, not exists, intersect...

I don't use MySQL I use MSSQL. I have always found it helpful to create views that filter items they way I want them and do all the heavy work. Then I write queries using those views.