Results 1 to 4 of 4


  1. #1
    Anastasis Guest

    Default INNER JOIN SQL

    I have two tables: "books" and "users". I want to relate the two so that I can display the book info, and on the same row display the user info. There is one field in the books table that correlates to the users table.<BR><BR>I have read around and this is the closest I can come to anything:<BR>&#060;begin code&#062;<BR>(Here i declare VBS and open my ODBC connection)<BR><BR>Set joinRS = Server.CreateObject("ADODB.Recordset")<BR>strSQLjo in = "SELECT * FROM books INNER JOIN users ON books.UserID = users.UserName"<BR>joinRS.Open strSQLjoin, conn<BR>&#060;end code&#062;<BR><BR>When I view the served up webpage I get this error msg:<BR>Error Type:<BR>Microsoft VBScript compilation (0x800A03F2)<BR>Expected identifier<BR>/bookswap/viewall.asp, line 15, column 41<BR><BR><BR>Any help given this beginner ASP programmer will be greatly appreciated. -<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: INNER JOIN SQL

    strSQLjoin = "SELECT * FROM books INNER JOIN users ON books.UserID = users.UserName" &#060;--is UserName a foreign key????<BR>i think it should be smth.--&#062; users.UserID<BR><BR>you can use it too:<BR>strSQLjoin = "SELECT * FROM books,users where books.UserID = users.UserName" <BR>

  3. #3
    Anastasis Guest

    Default RE: INNER JOIN SQL

    Thanks, Sil, for your post.<BR><BR>I didn&#039t realize until just a few minutes ago, before reading your post, that I had not provided that needed information.<BR><BR>In fact, I just discovered what primary keys and foreign keys are.<BR><BR>Neither of the fields are what I define as what I understand to be a primary key. But, knowing what I now know, that will change. Each table (books and users) has a primary numeric key. The field "UserID" in books relates to the field "UserName" in users. I am going to store User&#039s Names, Pwds, email, and phone in the User Table, and there will not be duplicates. The books table will contain book information and the User&#039s Name (UserID) that holds the book. (for more explanation, the site is a bookswap site). CURRENTLY, the books table has a primary numeric key, as well as the users table. I am going to change my users table and make the UserName field the primary key, since they cannot be duplicated anyway.<BR><BR>Now that THAT is done....I found a reference at<BR><BR>that goes into a bit more detail about joining tables.<BR><BR>I have now also discovered that I may either need a right or left join instead of just an inner join because I want the table to display ALL records of the books table and only the information from the users table that correlates.<BR><BR>*DEEP SIGH*<BR><BR>Another quick FYI - I am attending a 2yr tech college. This site I am attempting to create is the idea of one of my instructors. I have only taken VBScript, PERL, and am currently taking JScript and had just started learning ASP when I decided to switch to it from CGI processing instead for the site. I am learning and coding "on the fly" in efforts to get the site up and running ASAP.<BR><BR>Thanks again, Sil<BR>Anastasis<BR>

  4. #4
    Anastasis Guest

    Default Syntax Error - HELP!

    Okay, I tried this RIGHT JOIN thingy, I finally got past the errors because I forgot to DIM those variables I was using, and I get THIS:<BR><BR>Syntax error in query expression &#039users.* LEFT JOIN users ON books.UserID = users.UserName&#039.<BR><BR><BR>Here is my code: (PS Im using VBScript)<BR>&#060;begin code&#062;<BR>Set conn = Server.CreateObject("ADODB.Connection")<BR>conn.Op en "bookswap"<BR>Set joinRS = Server.CreateObject("ADODB.Recordset")<BR>strSQLjo in = "SELECT books.*, users.* LEFT JOIN users ON books.UserID = users.UserName"<BR>joinRS.Open strSQLjoin, conn<BR>&#060;end code&#062;<BR><BR>I included the other stuff around because maybe I even have the SQL method wrong. *shrug*.<BR>I want every field from both tables.<BR>Does ONE of the tables have to be referenced via a primary key?<BR><BR>Thanks ahead of time for any help given.<BR>Anastasis<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts