SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Evangelist tangledman's Avatar
    Join Date
    Sep 2005
    Location
    Puerto de Mazarron, Murcia, Spain
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    produce a matrix from three tables

    Hello Everyone...the sun is shining and it's sandal weather here in Spain, but I am in the office staring at the screen scratching my head. Could be fleas, could be this little problem.

    I have three database tables as follows;

    development - consists of the following fields development_id, development_name

    fileupload - consists of the following fields fileupload_id, development_id, fileupload_name, fileupload_path, document_type_id

    document_type - consists of the following fields document_type_id, document_type_name

    I'd like to produce a matrix of results to show at a glance what documents we have per development.





    There must be a way of writing a query to get this kind of output.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    not like that, nope

    try it like this --
    Code:
    select d.development_name
         , f.fileupload_name
         , f.fileupload_path
         , dt.document_type_name
      from development as d
    inner
      join fileupload as f
        on f.development_id = d.development_id
    inner
      join document_type as dt
        on dt.document_type_id = f.document_type_id
    order
        by d.development_name
         , f.fileupload_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist tangledman's Avatar
    Join Date
    Sep 2005
    Location
    Puerto de Mazarron, Murcia, Spain
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    R937,

    I haven't managed to try it yet, because I'm not quite sure what to put in the abbreviated part.

    select development.development_name
    , fileupload.fileupload_name
    , fileupload.fileupload_path
    , document_type.document_type_name
    from development as d?
    inner
    join fileupload as f?
    on fileupload.development_id = development.development_id
    inner
    join document_type as document_type
    on document_type.document_type_id = fileupload.document_type_id
    order
    by document.development_name
    , fileupload.fileupload_name

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    those were table aliases and you can see them referenced in my SELECT, ON, and ORDER BY clauses

    in the query you posted, where you are using the actual table names instead of table aliases, you would take the "as" part off completely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist tangledman's Avatar
    Join Date
    Sep 2005
    Location
    Puerto de Mazarron, Murcia, Spain
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello using the query


    select development.development_name
    , fileupload.fileupload_name
    , fileupload.fileupload_path
    , document_type.document_type_name
    from development as development_id
    inner
    join fileupload
    on fileupload.development_id = development.development_id
    inner
    join document_type
    on document_type.document_type_id = fileupload.document_type_id
    order
    by document.document_type_name
    , fileupload.fileupload_name

    against a database with the following structure


    CREATE TABLE `development` (
    `development_id` int(11) NOT NULL auto_increment,
    `tour_area_id` int(11) NOT NULL default '0',
    `builder_id` int(11) NOT NULL default '0',
    `development_name` varchar(255) collate latin1_general_ci NOT NULL default '',
    `country_id` int(11) NOT NULL,
    PRIMARY KEY (`development_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=216 ;



    CREATE TABLE `fileupload` (
    `fileupload_id` int(11) NOT NULL auto_increment,
    `PHONELISTid` int(11) NOT NULL default '1',
    `development_id` int(11) NOT NULL default '1',
    `builder_id` int(11) NOT NULL default '1',
    `document_type_id` int(11) NOT NULL default '0',
    `tour_area_id` int(11) NOT NULL default '0',
    `file_upload_path` varchar(255) NOT NULL default '',
    `file_upload_name` varchar(255) NOT NULL default '',
    PRIMARY KEY (`fileupload_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1384 ;



    CREATE TABLE `document_type` (
    `document_type_id` int(11) NOT NULL auto_increment,
    `document_type_name` varchar(50) NOT NULL default '',
    PRIMARY KEY (`document_type_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;







    gives the following error

    SQL query: Documentation

    SELECT development.development_name, fileupload.fileupload_name, fileupload.fileupload_path, document_type.document_type_name
    FROM development AS development_id
    INNER JOIN fileupload ON fileupload.development_id = development.development_id
    INNER JOIN document_type ON document_type.document_type_id = fileupload.document_type_id
    ORDER BY document.document_type_name, fileupload.fileupload_name
    LIMIT 0 , 30

    MySQL said: Documentation
    #1054 - Unknown column 'development.development_name' in 'field list'

    help.

  6. #6
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's because you used a table alias,

    Code:
    FROM development AS development_id
    using a table alias as R937 suggested, at least for me, is to shorten SQL statements.

    i wouldL:

    Code:
    FROM development AS dev
    then your SELECT statement would be

    Code:
    SELECT dev.development_name,
    makes sense?
    leo d.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if you want to use table aliases, see post #2

    if you don't want to use table aliases, see post #3, but change this --
    Code:
       from development as d?
    inner
      join fileupload as f?
    to this --
    Code:
       from development
    inner
      join fileupload
    as i suggested in post #4

    easy peasy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist tangledman's Avatar
    Join Date
    Sep 2005
    Location
    Puerto de Mazarron, Murcia, Spain
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK so I have removed the table aliases, which leaves me with;


    SELECT development.development_name, fileupload.fileupload_name, fileupload.fileupload_path, document_type.document_type_name
    FROM development
    INNER JOIN fileupload ON fileupload.development_id = development.development_id
    INNER JOIN document_type ON document_type.document_type_id = fileupload.document_type_id
    ORDER BY document.document_type_name, fileupload.fileupload_name

    This is giving the error

    #1054 - Unknown column 'fileupload.fileupload_name' in 'field list'

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    pretty sure you should have been able to suss this one out yourself

    your query says fileupload.fileupload_name but if you look in post #5 that's not the right way to spell it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist tangledman's Avatar
    Join Date
    Sep 2005
    Location
    Puerto de Mazarron, Murcia, Spain
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's clear to me now and it's my own stupid fault for naming my fields inconsistently.

    Thanks for your patience.

    OK I understand how the table alias thing works now...am I right in saying that it's used for shortening the query?

    The working version


    SELECT development.development_name, fileupload.file_upload_name, fileupload.file_upload_path, document_type.document_type_name
    FROM development
    INNER JOIN fileupload ON fileupload.development_id = development.development_id
    INNER JOIN document_type ON document_type.document_type_id = fileupload.document_type_id
    ORDER BY document_type.document_type_name, fileupload.file_upload_name

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist tangledman's Avatar
    Join Date
    Sep 2005
    Location
    Puerto de Mazarron, Murcia, Spain
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Even though the query works, I can't see how I can get this into the matrix, as per my first post.

    because a row is;

    development_name file_upload_name file_upload_path document_type_name

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    create the matrix in php (sorry i can't help with that part, as i don't do php, i do coldfusion)
    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
  •