creating select to make a pulldown that exludes already selected

gfrank

I am trying to write a select that will return a list of categories for a pulldown menu. But I want to make this list of options so it does NOT show categories that have already been seelcted. (So they can select only one)

For instance, if an company is already associated with the 'construction' category, I don't want 'construction' to show up in the pulldown of options for other catagories to associate this company with.

What I have is a table of category-name / id pairs:
The table looks like this...

Table Name : catagories
record columns: cat_id, cat_name, cat_belongs
cat ID is the unique id for a category, name is the name of the category, and cat_belongs is the id of the catagory that it is a sub of.

I have a table of companies
Table Name : companies
record columns: co_id, co_name... and others

Then I have another cross reference table
Table name: comp_cat_xref
record columns: xref_comp, xref_cat
xref_comp_is the id number of the company, and xref_cat is the id of the category associated with it.

So... what I want to do is select all category names and ids from the catagories table, but I want to exclude any catagory ids that are listed in the comp_cat_xref table for a particular company.

Here is what I tried to do....

SELECT categories.cat_name, categories.cat_id
FROM categories, comp_cat_xref
WHERE (categories.cat_id <> comp_cat_xref.xref_cat AND comp_cat_xref.xref_comp <> 10)
ORDER BY categories.cat_name ASC

(The 10 is an id of a company)
This query is not returning what I want because it returns multiple catagory names that are duplicates -- because it is comparing each entry in the xref table to each entry in the category table.

how can I get it to return Just ONE of each category name and ID?

is there a "unique" command or some other way to format this query to get a full list of all categories that only excludes the ones listed for the company in the cross referance table?

Thanks!

Gfrank

cwissy

there is the sql "DISTINCT" keyword

gfrank

Adding DISTINCT to the mySQL query does make the list smaller... but it is still selecting categories that it should not... I think because it is doing the distinct on the first part of the statement not on the selection criteria.

I tried this:

SELECT DISTINCT categories.cat_name, categories.cat_id
FROM categories, comp_cat_xref
WHERE (categories.cat_id != comp_cat_xref.xref_cat AND comp_cat_xref.xref_comp != 10)
ORDER BY categories.cat_name ASC

so.. that might be part of the answer... but not the whole thing. Anyone have any other suggestions?

cwissy

If each category is unique in the categories table then the following might work as it takes the whole of the categories table and returns only those rows that do not appear in the xref table for the conpany_id

select distinct cat_id, cat_name from categories c left outer join on comp_cat_xref x,companies s where c.comp_id<>$comp_id and c.cat_id=x.cat_id

so, what should be returned will be all the categories from the cross-reference table that do not have the company id $comp_id associated with them. Then the DB should cut it down to only unique ones, removing the duplicates.

This obviously means quite a bit of work for the DB as it has to collate all teh data from the xref table and then remove the superfluous duplicates.

gfrank

It just seems there must be a simpler way to do it... i am also not sure if the version of mysql that my hosting company is running will support the outer join