7 Replies Latest reply on Sep 13, 2008 7:12 AM by ed19

    Search multiple tables

    ed19 Level 1
      Hi,
      I am having some trouble and need help! There is a search text box from homepage where someone can search by typing a name and they would get the results from 2 different tables NAMES and LETTERS that have relationship via NID and NID. Need to show Name, Letters, and Date.

      Tables
      NAMES: NID, Name
      LETTERS: NID, LID, Letters, Date

      Don't know how to setup the recordsets on the Letterresults.asp page. I have both tables as 2 different recordsets under applications and need to show
      Name, Letters, and dates only but because they are from 2 different tables I am stuck not knowing what code to put into which recordset ? Do I put the same code in both rsNames and rsLetters? Do I need both recordsets there?

      In rsNames

      SELECT *
      FROM Names
      WHERE Name LIKE %MMColParam%

      In rsLetters

      SELECT *
      FROM Letters

      This brings up all the records from the db not just the name you need. I tried INNER JOIN but that gave errors so I went backward
      I'm stuck
        • 1. Re: Search multiple tables
          Level 7
          > I am having some trouble and need help! There is a search text box from
          > homepage where someone can search by typing a name and they would get the
          > results from 2 different tables NAMES and LETTERS that have relationship
          > via
          > NID and NID. Need to show Name, Letters, and Date.
          >

          Sounds like you need to join the 2 tables, eg

          SELECT Name, Letters, [Date]
          FROM Names N INNER JOIN Letters L
          ON N.nid = L.nid
          WHERE Name LIKE %Whatever%

          I've also enclosed date in [ ] - using a column called date in a query
          without [] will usually cause an error

          Does that get you any further forward?

          Cheers,
          Jon


          • 2. Re: Search multiple tables
            bregent Most Valuable Participant
            You only need one recordset. Use a join clause to join the tables to each other.
            • 3. Re: Search multiple tables
              ed19 Level 1
              Thanks guys, Unfortunately I can't get to my computer till later today and I am eager to put that code in and try it and let you know can you please check back here tomorrow again.

              I do have questions though,

              for the date I would like to only show dates that are newer than the current date
              >=Date[]
              where would I put this date expression?

              Also from your post Jon
              >FROM Names N INNER JOIN Letters L
              >ON N.nid = L.nid

              are the N and L alias? If yes, what do they help with?

              Bregent mentioned I only need 1 recordset, which one do I keep? the rsNames? When I drag and drop the links from the application window into DW design view won't I need both recordsets so I can drag the fields from each table or am I doing that wrong?


              As you can see I am struggling, and I ask many questions (bad habit I guess)
              • 4. Re: Search multiple tables
                ed19 Level 1
                okay but now 4 records show up for John, but all 4 are showing the same letter and same date which is actually the first record in my db. So the first part of the db works fine but now the letters table is always showing the 1st record in the db.




                • 5. Re: Search multiple tables
                  bregent Most Valuable Participant
                  Without seeing your database schema and sample data, we are going to have a hard time with this. Please post more information regarding your database tables.
                  • 6. Search multiple tables
                    ed19 Level 1
                    ok
                    • 7. Search multiple tables
                      ed19 Level 1
                      .