Web Analytics Made Easy -
StatCounter Join and Aliases - CodingForum

Announcement

Collapse
No announcement yet.

Join and Aliases

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Join and Aliases

    This is my query

    Code:
    SELECT tblTasks.*, `to`.*, `from`.*, DATE_FORMAT(fldDate, '%d/%m/%Y %H:%i') as formatteddate FROM tblTasks INNER JOIN tblAdminUsers as `from` on fldFrom = `from`.fldUserID inner join tblAdminUsers as `to` on fldTo = `to`.fldUserID
    as you can see I'm double joining to tblAdminUsers to get all the details of the sender and reciever.
    However when I get the results of the query there is no way to distinguish between the sender and the receivers details
    I expected the results to come back as eg. to.FullName, from.FullName etc - I'm sure they have in the past - but they havent here. Am I going to have to specify all the fields names separately and give them aliases?
    http://www.hazelryan.co.uk

  • #2
    I don't know of any other way...
    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com

    Comment


    • #3
      I really don't know why you wouldn't want to spell out the fields you want to select. The wildcard is really not a good idea for maintainable code.

      Comment


      • #4
        Originally posted by Fumigator View Post
        I really don't know why you wouldn't want to spell out the fields you want to select. The wildcard is really not a good idea for maintainable code.
        Maintainability is the exact reason for using the wildcard - specifying individual fields means changing the query every time you want more information - this particular application is built on a templating system, the code knows nothing about the display but if the designer wants to show a piece of information on the page they just need to put <!--fieldname--> into the template and it will appear - assuming the value was pulled out from the query. If each of the fieldnames were specified individually then 1 the query would be about 4 lines long and 2 it would have to be changed every time the design was changed.
        http://www.hazelryan.co.uk

        Comment


        • #5
          somewhere, i'm not getting the idea.
          if you want to use a template where you just refer to the <!--fieldname--> , then it would precisely be a good idea to use aliases for the fields in your resultset. because then, you can change the underlying db-structure or querys, and all you then need to change is the selectstatement.
          i would suggest creating a view with that 4 lines long select, use aliases for the columns inside that select, and just have a "select * from view" inside your applicationlayer.

          this way, the resultsets are kept as small as possible and changes to the dbstructure wount have any effect on your application (PHP?) or presentation (template, css) layer --> you'd only have changes in your datalayer.
          Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

          Comment


          • #6
            Its far more likely that the designer will want to change the information displayed in various views than it is that I would change the database structure....
            How often do you go around changing the fieldnames in your databases?

            I guess it all depends on your situation - I'm working in a situation where presentation is far more likely to change than data structure.
            http://www.hazelryan.co.uk

            Comment


            • #7
              Originally posted by NancyJ View Post
              How often do you go around changing the fieldnames in your databases?
              well, actually, that is not so uncommon. as requirements evolve, it's fairly normal that db-structures get optimised through all sorts of reorganisations.
              the most common reason to not change the structure is "we don't know what will be impacted so just leave it as it is"
              if you don't have some middletier for your data, then it's, in my experience, a very good idea to use views.

              and if your designer needs additional data that is not included in the view, then it's just a matter of altering the view. you can even automate that with a few lines of PHP-code so that the designer can do this himself.

              but in my opinion, there are no valid excuses for including fields in your resultset, that are not used in your application.
              Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

              Comment


              • #8
                Originally posted by raf View Post
                but in my opinion, there are no valid excuses for including fields in your resultset, that are not used in your application.
                Thats your opinion and you're entitled to it but I dont share it.
                http://www.hazelryan.co.uk

                Comment

                Working...
                X