3 Replies Latest reply on Feb 2, 2008 8:23 PM by Dan Bracuk

    Selecting Multiple rows

    wbnc1902
      Ok I have three tables which two tables are one to one and the other one is a one to many. The one to one tables have all the data in one row and the one to many has the data in multiple rows. The one to many table (named county_notes) has notes about specific counties. The other tables (named county_bio and county_stats) have different information about each county. The three tables are connected by an ID.

      What I want to be able to list the county information and the notes about each county in the same report.

      For example:

      Record1
      County: Craven
      Phone: (252)123-4567
      Notes:
      Judy is nice
      John is mean

      The tables would like this:

      county_bio
      ID county_name
      1 craven


      county_stats
      ID county_phone
      1 (252) 123-4567

      county_notes
      ID notes
      1 Judy is nice
      1 John is mean


      Obviously the above tables are just examples but you get the idea of how the tables are set up. The problem is I can join the county_notes table to one of the two using tables, so that is problem one. Problem 2, Coldfusion separates each note into a different record. So I end up with record "craven" showing up twice (once with the note about judy, and once with the note about john).

      Here is the SQL that joins county_bio and county_notes:
      SELECT *
      FROM county_bio
      LEFT JOIN county_notes
      ON county_bio.ID = county_notes.county_ID

      First how do I get all three tables connected? And second how do I get ColdFusion to print each note about the county in one record?

      Thanks for any and all help! I have been working on this for a few days and am out of ideas.
        • 1. Re: Selecting Multiple rows
          Level 7
          wbnc1902 wrote:
          > First how do I get all three tables connected?

          something like this:

          SELECT *
          FROM (county_bio cb INNER JOIN county_stats cs ON cb.ID = cs.ID)
          LEFT JOIN county_notes cn ON cb.ID = cn.county_ID

          NOTE: it is not the best practice to use SELECT *. you should instead
          list actual columns you need selected.

          > And second how do I get
          > ColdFusion to print each note about the county in one record?

          look up the GROUP attribute of CFOUTPUT tag in the docs

          hth

          ---
          Azadi Saryev
          Sabai-dee.com
          http://www.sabai-dee.com
          • 2. Re: Selecting Multiple rows
            wbnc1902 Level 1
            THANK YOU SO MUCH!!!! This was a big help. Forgive me for being new at SQL but can you explain why there is a ( ) around the inner join in the from statement? My only guess is it is some sort of subquery that joins those two tables together into one virtual table and then joins that table with the county_notes table creating one big virtual table.

            Am I even close?

            Thanks again!


            Also thanks for keying me into to the group attribute of the cfoutput statement.
            • 3. Re: Selecting Multiple rows
              Dan Bracuk Level 5
              quote:

              Originally posted by: wbnc1902
              THANK YOU SO MUCH!!!! This was a big help. Forgive me for being new at SQL but can you explain why there is a ( ) around the inner join in the from statement? My only guess is it is some sort of subquery that joins those two tables together into one virtual table and then joins that table with the county_notes table creating one big virtual table.

              Am I even close?


              In this particular case, the brackets were not necessary. But, obviously they didn't hurt.

              The concept you are describing has many names. I call it joining to a subquery. Others call it creating tables on the fly or derived tables. The syntax is

              select stuff
              from a_table join (select etc) alias_name on a_table.field = alias_name.field
              etc