On Tuesday 03 January 2006 6:11 pm, Legolas Woodland wrote:
> Thank you for reading my post
> I have 3 tables like the above images
> a web sites , a category and a relation between them.
> I defined a relation between web sites and categories in table
> website_categories
> so if a web site incoperate in a category then its id with category id
> will be there
>
Its one thing to help someone understand how to use Derby,
its another to actually consult on a project....
Having said that....
Your diagram is missing some details.
It sounds like you're attempting to have a many to many relationship between
the websites and the categories. That is, each category can contain many
websites and each website may be in multiple categories.
> I want to return categories that an specefic web site in not in it.
>
> :( i tried every combination of joins and where cluases but i never
>
> recieved correct categories.
>
> can some one pleas help ?
> Thank you
Well, it seems pretty straight forward.
If you think about it, based on a website_id, you already know the categories
that its in. So all you need is a list of categories which are not already
associated with the table.
Based on your diagram, you need to do a join between the category table and
your w_c table.
Something like this...
SELECT category_id, category_name
FROM category a, web_cat b
WHERE a.category_id = b.category_id
AND category_id NOT IN ( SELECT category_id
FROM web_cat
WHERE website_id = ?)
Should do it.
But hey, what do I know?
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
[EMAIL PROTECTED]
(312) 952-8175 [mobile]
