Joining 5 table

Databases
1

I need to join 5 tables somehow but can’t figure how to do it. Or maybe is my complete setup wrong. Anyway this are the tables involved:


CREATE TABLE IF NOT EXISTS `companies` (
  `company_id` int(4) unsigned NOT NULL auto_increment,
  `category_id` int(2) NOT NULL,
  `company_name` varchar(64) NOT NULL,
   PRIMARY KEY  (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(2) NOT NULL auto_increment,
  `category` varchar(32) default NULL,
   PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `activities` (
  `activity_id` int(4) NOT NULL auto_increment,
  `activity` varchar(64) default NULL,
   PRIMARY KEY  (`activity_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=91 ;

CREATE TABLE IF NOT EXISTS `companies_activities` (
  `company_id` int(4) unsigned NOT NULL,
  `activity_id` int(4) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `categories_activities` (
  `category_id` int(2) NOT NULL,
  `activity_id` int(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

These are the points i’m struggling with:

  1. A company is in one category (category_id key in companies table).
  2. A company can have multiple activities. (table company_activities)
  3. There are multiple activities in a category (categories_activities)

The problem in constructing the right JOIN has to do with point 1 in the above list. As I said the company is by default in one category. But like I said the company can have multiple activities and those activities can be in multiple categories. And this is the point where I get confused.

I would like to output a listing with suggestions for certain categories. Suggestions for the kids, for a rainy day, adventurers etc. I have for example a company which is in the category Water Sport but they have activities which are related to the category Children Activities I would like this company with those specific activities to show up in my listing of suggestions for the kids.

Is this possible or should I make changes to the database?

Thank you in advance.

2

please explain

which suggestions? for which categories?

3

For example the activity Banana Boat Ride is in multiple categories (Water Sport and Children Activities: table categories_activities) Most companies organizing the Banana Boat Ride have a category_id relating them to Water Sport(category_id 16 in the database).

I would like to to output a listing with companies organizing children activities (category_id 3 in the database)

When I would just join companies and categories and use WHERE category_id = 3 the company will never show up in the listing since they have category_id 16. So some how I need to create a relation for this company with the category Children Activities through the activity, which is related to the category Children Activities

4

Why do you give a category to the company, if that company is doing activities that belong to other categories? What is the use of the category in the companies table?

5

When I took over this project that key was already there. I need to think very deep If I can take it away without compromising anything else. What would be your suggestion in this case?

6

Ignore it :smiley:
Start your search for companies based on category from the activities table

7

:slight_smile: I just had a quick look, and I probably could do that since category_id in the companies table is only used for the search facility indeed.

But how do I relate the companies to the different categories or should that be done through the activities they represent? If so how?

8

okay, let’s do this one

start with children’s categories

  FROM categories
 WHERE categories.category LIKE '%children%'

this allows you to start with categories that you don’t know the id of (if you know the id, you can skip one join)

get all activities for each category

INNER
  JOIN categories_activities
    ON categories_activities.category_id = categories.category_id

then get all companies for each activity

INNER
  JOIN companies
    ON companies.company_id = categories_activities.company_id

putting it all together, you’ll need a DISTINCT since a single company can have multiple activities in the same category

SELECT DISTINCT companies.company
  FROM categories
INNER
  JOIN categories_activities
    ON categories_activities.category_id = categories.category_id
INNER
  JOIN companies
    ON companies.company_id = categories_activities.company_id
 WHERE categories.category LIKE '%children%'

vwalah, companies organizing children activities

9

thank you so much rudi only this one is breaking me up:


  JOIN companies
    ON companies.company_id = categories_activities.company_id

categories_activities should be companies_activities and these kind of things are always breaking me up, because I don’t know what to change now

10

oh yeah, you’re right :blush:

try this –

SELECT DISTINCT companies.company
  FROM categories
INNER
  JOIN categories_activities
    ON categories_activities.category_id = categories.category_id
INNER
  JOIN companies_activities
    ON companies_activities.activity_id = categories_activities.activity_id
INNER
  JOIN companies
    ON companies.company_id = companies_activities.company_id
 WHERE categories.category LIKE '%children%'
11

Works as a charm rudy. Thank you so much :tup: