Perform join to multiple tables with one query--possible?

[Ocean Breeze]

The object of all of this is to create a table that lists a document title, it's file type, size, and the date it was last modified sorted by sections and grouped by category grouped by project for each client.

I can get bits and pieces but then I start confusing myself! For example,

SELECT * FROM (documents left join document_sections on documents.document_id =  document_sections.document_id) left join sections on document_sections.section_id = sections.section_id;

gets me the title, type, size, date modified, and section. And I can retrieve title, type, size, date, and category or title, type, size, date, and project, etc.

I start getting confused (or maybe I'm already confused!) trying to figure out how to "join" it altogether.

I would GREATLY appreciate any nudges in the right direction.

Thanks so much!

Cheers!
deb

The web hosting company we're using has mysql server version 4.1.14

Here are the pertinent tables in the database.

categories
category_id int(11) Not Null auto_increment
category varchar(50) Not Null
PRIMARY KEY: category_id

clients
client_id int(11) Not Null
username varchar(20) Not Null
pass varchar(50) Not Null
client_name varchar(40) Not Null
PRIMARY KEY: client_id

document_categories
doc_cat_id int(11) Not Null
document_id int(11) Not Null
category_id int(11) Not Null
PRIMARY KEY: doc_cat_id

document_sections
doc_sec_id int(11) Not Null
document_id int(11) Not Null
section_id int(11) Not Null
PRIMARY KEY: doc_sec_id

documents
document_id int(11) Not Null
document_name varchar(60) Not Null
document_type varchar(30) Not Null
document_size varchar(15) Not Null
date_last_modified date Not Null
project_id int(11) Not Null
date_entered timestamp Not Null
description varchar(100) Not Null
filename varchar(30) Not Null
PRIMARY KEY: document_id

projects
project_id int(11) Not Null
project_name varchar(60) Not Null
client_id int(11) Not Null
PRIMARY KEY: project_id

sections
section_id int(11) Not Null
section varchar(20) Not Null
PRIMARY KEY: section_id

[r937]

you neglected to mention what it is you're trying to accomplish with your query

what is the purpose of the document_id column in the categories table?

if the document_sections table relates documents to sections, what is the purpose of the document_id column in the sections table?

also, the doc_cat_id and doc_sec_id columns are useless and counter-productive

[Ocean Breeze]

Thanks for your quick reply!

you neglected to mention what it is you're trying to accomplish with your query

The object of all of this is to create a table that lists a document title, it's file type, size, and the date it was last modified sorted by sections and grouped by category grouped by project for each client.

what is the purpose of the document_id column in the categories table?

Nothing! Sorry, I copied it from the original db design doc. It has been removed. The same with the sections table. I've updated the tables in my original post. Thanks for catching that.

if the document_sections table relates documents to sections, what is the purpose of the document_id column in the sections table?

None-it's gone! (explanation above)

also, the doc_cat_id and doc_sec_id columns are useless and counter-productive

Should I just be using document_id?

It's been a while (years) since I've worked with databases. Can you tell?

[r937]

...sorted by sections and grouped by category grouped by project for each client.

this part is confusing

what does "grouped" mean? are you looking for a multi-level sort?

if your tables have been modified, would you mind doing a SHOW CREATE TABLE for each one please

[Ocean Breeze]

this part is confusing
what does "grouped" mean? are you looking for a multi-level sort?

Each client has multiple projects. Some projects have multiple categories, others aren't categorized at all. Some documents that relate to a project may pertain to only a certain section (table of contents, report, table, figure, appendix, etc.) of the final report, so I'd like to group by section when they exist.

Maybe a picture will help.




And this is the prototype of the final layout on the web. Note: there are no categories or sections on this prototype, they were added later.

if your tables have been modified, would you mind doing a SHOW CREATE TABLE for each one please

Not at all. Thanks for your time in looking at this and helping--I really appreciate it.

CREATE TABLE IF NOT EXISTS `documents` (
  `document_id` int(11) unsigned NOT NULL auto_increment,
  `document_name` varchar(60) collate utf8_unicode_ci NOT NULL default '',
  `document_type` varchar(30) collate utf8_unicode_ci NOT NULL default '',
  `document_size` varchar(15) collate utf8_unicode_ci NOT NULL default '',
  `date_last_modified` date NOT NULL default '0000-00-00',
  `project_id` int(11) unsigned NOT NULL default '0' ,
  `date_entered` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `description` varchar(100) collate utf8_unicode_ci NOT NULL default '',
  `filename` varchar(30) collate utf8_unicode_ci NOT NULL default '' ,
  PRIMARY KEY  (`document_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
 
CREATE TABLE IF NOT EXISTS `clients` (
  `client_id` int(11) unsigned NOT NULL auto_increment,
  `username` varchar(20) character set latin1 collate latin1_bin NOT NULL default '',
  `pass` varchar(50) NOT NULL default '',
  `destination` varchar(64) NOT NULL default '' ,
  `client_name` varchar(40) NOT NULL default '' ,
  PRIMARY KEY  (`client_id`),
  UNIQUE KEY `client_name` (`client_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;
 
CREATE TABLE IF NOT EXISTS `projects` (
  `project_id` int(11) unsigned NOT NULL auto_increment,
  `project_name` varchar(60) collate utf8_unicode_ci NOT NULL default '',
  `client_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`project_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=40 ;
 
CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(11) unsigned NOT NULL auto_increment,
  `category` varchar(50) collate utf8_unicode_ci NOT NULL default '',
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;
 
CREATE TABLE IF NOT EXISTS `sections` (
  `section_id` int(11) unsigned NOT NULL auto_increment,
  `section` varchar(20) collate utf8_unicode_ci NOT NULL default '' COMMENT 'document section names, used for sorting documents under a project name',
  PRIMARY KEY  (`section_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=52 ;
 
CREATE TABLE IF NOT EXISTS `document_categories` (
  `doc_cat_id` int(11) unsigned NOT NULL auto_increment,
  `document_id` int(11) unsigned NOT NULL default '0',
  `category_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`doc_cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
 
CREATE TABLE IF NOT EXISTS `document_sections` (
  `doc_sec_id` int(11) unsigned NOT NULL auto_increment,
  `document_id` int(11) unsigned NOT NULL default '0',
  `section_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`doc_sec_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
 
--

[r937]

yes, you meant a multi-level sort

(it is potentialy misleading to use the term "grouped by" in this context, as GROUP BY in SQL has a completely different function from sorting)

okay, a client can have one or more projects -- this is a one-to-many relationship

a project can have one or more documents -- another one-to-many relationship

but a document can belong to multiple categories and a category can have multiple documents -- a many-to-many relationship

and a document can belong to multiple sections and a section can have multiple documents -- also a many-to-many relationship

unfortunately, pulling everything together in a single query is not as simple as it would appear

your illustration shows sections within categories, but unfortunately there is no relationship in the tables to support this

my advice is to see if you can actually load the data into your tables that reflects this, and i think you'll see it's not possible

so you'll need to rethink the categories and sections part of the design


as for the unnecessary columns, here's what the many-to-many tables should look like (assuming you need both of these tables) --

CREATE TABLE document_categories 
( document_id INTEGER UNSIGNED NOT NULL 
, category_id INTEGER UNSIGNED NOT NULL 
, PRIMARY KEY (document_id, category_id)
); 

CREATE TABLE document_sections 
( document_id INTEGER UNSIGNED NOT NULL 
, section_id  INTEGER UNSIGNED NOT NULL 
, PRIMARY KEY (document_id, section_id)
);

the auto_increment columns are unnecessary and counter-productive

[Ocean Breeze]

yes, you meant a multi-level sort

(it is potentialy misleading to use the term "grouped by" in this context, as GROUP BY in SQL has a completely different function from sorting)

Thanks for clarifying that!

okay, a client can have one or more projects -- this is a one-to-many relationship

a project can have one or more documents -- another one-to-many relationship

Yes

but a document can belong to multiple categories and a category can have multiple documents -- a many-to-many relationship

and a document can belong to multiple sections and a section can have multiple documents -- also a many-to-many relationship

No, actually. The way these documents are written each document belongs to only one category. A category can have several documents. The same with section--each document is only one section, but each section can have multiple documents.

unfortunately, pulling everything together in a single query is not as simple as it would appear

Well, at least I was stumped by something that's not actually simple and obvious!

your illustration shows sections within categories, but unfortunately there is no relationship in the tables to support this

my advice is to see if you can actually load the data into your tables that reflects this, and i think you'll see it's not possible

so you'll need to rethink the categories and sections part of the design

Do you have any suggestions on how best to do this? I kept them as separate tables (lookup tables?) because I thought that was the best db design. I didn't want to add a category_id and section_id to the documents table because not every document will have either a category or a section. Each are used only occasionally and I didn't want a table full of columns with no data. The only way I could think of to link the two was the document_categories and document_sections tables.

Now my problem is pulling it all together.

Does it make sense to do a join, create a temporary table, join to that and create another temporary table, etc. until I've pulled in all the fields I need?

I don't really understand them, but would a subquery be the way to go?

I've also seen examples of nested joins, but can't for the life of me wrap my head around them--yet. I'll keep plugging away. The light bulb comes on eventually!

as for the unnecessary columns, here's what the many-to-many tables should look like (assuming you need both of these tables) --

CREATE TABLE document_categories 
( document_id INTEGER UNSIGNED NOT NULL 
, category_id INTEGER UNSIGNED NOT NULL 
, PRIMARY KEY (document_id, category_id)
); 

CREATE TABLE document_sections 
( document_id INTEGER UNSIGNED NOT NULL 
, section_id  INTEGER UNSIGNED NOT NULL 
, PRIMARY KEY (document_id, section_id)
);

the auto_increment columns are unnecessary and counter-productive

Thanks for this, but these are actually one-to-many relationships.

I really appreciate your thoughtful responses. It's been so long since I've done this. I start thinking I'm on the right track and then I just confuse myself. It's nice having someone explain it all and give me some input.

Cheers!

[r937]

The way these documents are written each document belongs to only one category. A category can have several documents. The same with section--each document is only one section, but each section can have multiple documents.

in that case i would urge you to ditch the many-to-many tables, and put the category_id and section_id columns into the documents table, and make them nullable (i.e. NULL, not NOT NULL)

then you are left only with the issue of whether a section belongs to only one category or whether a section can be in multiple categories