Query across multiple tables

goobers

Hi,

I have a situation as follows:

There are four tables involved, first being "items", second "category", third "category items", and fourth being "user category"

Items contains:

ItemID

ItemName

Category contains:

CategoryID

CategoryName

Category Items contains:

ItemID

CategoryId

Category Type

User Category contains:

ID
UserID

Category ID

Now, this is the logic. Items can either be in a specific 'category' or they can be under full scope. Meaning that an item doesn't necessarily have to belong to a category. Now a user can be part of a category, but doesn't nessicarily have to be part of one.

I'm trying to do the following:

Select ALL items EXCEPT items that belong to categories for which a particular user is NOT a part of IF the category type is 'restricted'. (an enum, or integer, doesnt matter).

Essentially I have to query across those four tables to get my result, but I don't know how. Any help is greatly appreciated.

Thanks in advance !

tomhath

You're making it difficult by defining what you want as a double negative ("EXCEPT items that belong to categories for which a particular user is NOT a part of IF the category type is 'restricted'"...)

What you want are items for the categories the user belongs to, plus all other items where the category type is something besides 'restricted'.

The first half is easy, just join to the CategoryItems and UserCategory tables. The second part is also easy if you ignore the user's categories (which you can because the first part already selected them). So just UNION the results together to get the distinct set of items. This is untested, but should be close:

select i.*
from Items i
inner join CategoryItems ci
    on ci.ItemID = i.ItemID
inner join UserCategory uc
    on uc.CategoryID = ci.CategoryID
where UserID = xxxxxx
UNION
select i.* 
from Items i
inner join CategoryItems ci
    on ci.ItemID = i.ItemID
inner join Category c
    on c.CategoryID = ci.CategoryID
where c.categoryType <> 'restricted'

goobers

Hi,

Thanks, but if I understood the query correctly, what about all the items that arent part of a category ?

tomhath

Yes, I missed that part. The simplest way is to pick them up with another UNION, might be a more clever solution, but it's getting kind of late to think clearly.

UNION
select i.*
from Items i
where not exists (
    select 1 from CategoryItems ci
    where ci.ItemID = i.ItemID
    )

goobers

Thanks ! I'll do that for now. I guess if you (or anyone else) can think of a more elegant way to do this, please post !

Thank you very much tomhath