Strange / Difficult Query

[Anon]

Hi. First of all sorry for my topic title but i didn't know what to use in my case 🙂

I have a job offering application with MySQL. My problem is the following.
I have the main categories listed. When selected it should get me all the subcategories along with the nationalities that are available for each subcategory. I can't figure out how to do this or if it can be done at all. If each person went in only one category that would be easy. But i have them in several subcats.

Currently i have the following tables(among others) :
-people_tbl for all the candidate's information(including nationality)
-people_categories in which each person's categories that belongs are kept
-categories_tbl for all the main categories and the sub categories

Is it possible to come up with a solution to this or should i forget it?

Thanks a lot.

John

[Anon]

Should be pretty simple, although you haven't given enough information yet. I get the feeling it might be cleaner with a fourth table to hold the subcategories but they could be stored in the one table you already have. After that it's just a JOIN on the relevant IDs.

[Anon]

The problem is that there are more than one categories for each person. With a fourth table that keeps the id, categories and nationality i'd get a lot of duplicate data. Beside that it would be extremely difficult to put this in the db.

If there are any ideas available, i'd appreciate them.

Thanks a lot

John

[tomhath]

You just have a four way JOIN, what's strange or difficult about it? I'm guessing at column names:

select distinct p.nationality, sc.subcategory
from people_tbl p, people_categories pc, categories c, categories sc
where c.cat = $selectedcat
and sc.catid = c.id
and pc.subcatid = sc.id
and p.id = pc.peopleid