4 Replies Latest reply on Oct 20, 2006 7:57 AM by paross1

    join 2 tables

    JohnGree
      Hi i have 2 table

      **TimesTable**
      TimesID
      Times


      **ClientsTable**
      ClientID
      AppTimeID
      Name

      what i want to do is show all the "Times.Times" in a list which i have done with a cfoutput query

      then if "ClientsTable.AppTimeID" matches the "Times.Times" show it next to the time in the output

      ie
      08:00 John
      09:00
      10:00 Paul
      11:00

      do i need to join the query or use a cfif tag?
        • 1. Re: join 2 tables
          Level 7
          A join should work:

          SELECT t.TimesID, t.Times, c.ClientID, c.AppTimeID, c.Name
          FROM TimesTable t
          LEFT JOIN ClientsTable c
          ON t.Times = c.AppTimeID
          ORDER BY t.Times


          --
          Ken Ford
          Adobe Community Expert
          Fordwebs, LLC
          http://www.fordwebs.com


          "JohnGree" <webforumsuser@macromedia.com> wrote in message
          news:ehaeug$57l$1@forums.macromedia.com...
          > Hi i have 2 table
          >
          > **TimesTable**
          > TimesID
          > Times
          >
          >
          > **ClientsTable**
          > ClientID
          > AppTimeID
          > Name
          >
          > what i want to do is show all the "Times.Times" in a list which i have
          > done
          > with a cfoutput query
          >
          > then if "ClientsTable.AppTimeID" matches the "Times.Times" show it next to
          > the
          > time in the output
          >
          > ie
          > 08:00 John
          > 09:00
          > 10:00 Paul
          > 11:00
          >
          > do i need to join the query or use a cfif tag?
          >


          • 2. Re: join 2 tables
            Dan Bracuk Level 5
            Suggestion John, learn about many to many relationships.
            • 3. Re: join 2 tables
              Level 7
              Dan,

              IMO telling someone to learn about something does not help them much.
              Giving them a place to look for the answer would be much more helpful.

              BTW I think it would be a one-to-many relationship since he has one times
              table record that can be in many clients table records.

              --
              Ken Ford
              Adobe Community Expert
              Fordwebs, LLC
              http://www.fordwebs.com


              "Dan Bracuk" <webforumsuser@macromedia.com> wrote in message
              news:ehahk6$8hu$1@forums.macromedia.com...
              > Suggestion John, learn about many to many relationships.


              • 4. Re: join 2 tables
                paross1 Level 2
                It would become a many-to-many relationship if he was to allow a client to have more than one App time (which I assume is some kind of appointment time), at which time he would want to consider migrating AppTimeID and ClientID to an associative entity.

                Phil