SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Location
    Markham, ON, Canada
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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,

    Code MySQL:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Location
    Markham, ON, Canada
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your quick reply!

    Quote Originally Posted by r937 View Post
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ocean Breeze View Post
    ...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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Location
    Markham, ON, Canada
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.


    Quote Originally Posted by r937 View Post
    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.

    Code MySQL:
    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 ;
     
    --

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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) --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Location
    Markham, ON, Canada
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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!

    Quote Originally Posted by r937 View Post
    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

    Quote Originally Posted by r937 View Post
    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.

    Quote Originally Posted by r937 View Post
    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!

    Quote Originally Posted by r937 View Post
    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!

    Quote Originally Posted by r937 View Post
    as for the unnecessary columns, here's what the many-to-many tables should look like (assuming you need both of these tables) --
    Code:
    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!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ocean Breeze View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •