db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <de...@segel.com>
Subject Re: can you help me to solve a query problem which 3 tables are engaged in it?
Date Wed, 04 Jan 2006 02:09:20 GMT
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.
derby@segel.com
(312) 952-8175 [mobile]

Mime
View raw message