SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  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,341
    Mentioned
    63 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,341
    Mentioned
    63 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,341
    Mentioned
    63 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"


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
  •