none
How do I use INNER JOIN with three tables?

    Question

  •  

    Hi there,

     

    I have three tables: tblReport, lkuPrinter, and lkuProblem.

     

    tblReport columns are as follows:

     

    ReportID          Date          PrinterID          ProblemID

     

     

    lkuPrinter is as follows:

     

    Printer ID          PrinterName

     

     

    lkuProblem is as follows:

     

    ProblemID       ProblemDescription

     

     

     

    I want to join the data to display a table that has the following information:

     

    Date        PrinterName           ProblemDescription

     

     

     

    How do I do this?  Or please give me a similar example.  Thanks.

     

    Wednesday, May 16, 2007 2:57 PM

Answers

  • dotnetme,

     

    First of all, thanks for your help.  I'm all set now.  Here is what I did:

     

    Since the database was created with MS-Access I opened it up in Access and created a Query via the Wizard.  I was able to create the report that I want.  I then went to View SQL and got my Select statement.  Here it is:

     

    Code Snippet

     SELECT tblReport.Date, lkuPrinter.PrinterName, lkuProblem.ProblemDescription
    FROM lkuProblem INNER JOIN (lkuPrinter INNER JOIN tblReport ON lkuPrinter.PrinterID = tblReport.PrinterID) ON lkuProblem.ProblemID = tblReport.ProblemID

     

    Have a Great one!

    Thursday, May 17, 2007 1:29 PM

All replies

  • Here try something like this....  this statement will list everything in the tables. use the WHERE clause to narrow the data... like Where Date = or > then a given date... or WHERE PrinterID = 1234 or WHERE ProblemDescription = .....something.....

     

    Code Snippet

     

    Select a.date,b.PrinterName,c.ProblemDescription
    From tblReport AS a
    Inner Join lkuPrinter AS b on a.PrinterID = b.PrinterID
    Inner Join lkuProblem AS c on a.ProblemID = c.ProblemID

     


    dotnetme

    Wednesday, May 16, 2007 3:26 PM
  • dotnetme,

     

    Thanks for your reply.  I'm getting the following error: Microsoft JET Database Engine error '80040e14'

    Syntax error in FROM clause.

     

    Here is my Select statement:


    Select tblReport.Date, lkuPrinter.PrinterName, lkuProblem.ProblemDescription

     From tblReport AS tblReport

     Inner Join lkuPrinter AS lkuPrinter.PrinterName ON tblReport.PrinterID = lkuPrinter.PrinterID

     Inner Join lkuProblem AS lkuProblem.ProblemDescription ON tblReport.ProblemID = lkuProblem.ProblemID

     

    I am completely new to this.  Am I missing something like parentheses?  Thanks.

    Wednesday, May 16, 2007 5:14 PM
  • newbies welcome! You got to start somewhere...

     

    First are using the SQL Query Analyzer window to execute this statement???

     

    2nd This is redundate statement "From tblReport AS tblReport" only use the "AS" Statement if you wish to temporaily change the table name like this "From SuperLongTableName AS tbl1" so you don't have to type SuperLongTableName over and over....

    anyway with out changing the the table try it this way...  but i would still look to know where are executing the statement...

     

    Code Snippet

    Select tblReport.Date, lkuPrinter.PrinterName, lkuProblem.ProblemDescription

     From tblReport

     Inner Join lkuPrinter ON tblReport.PrinterID = lkuPrinter.PrinterID

     Inner Join lkuProblem ON tblReport.ProblemID = lkuProblem.ProblemID

     

    dotnetme

     

    Wednesday, May 16, 2007 6:01 PM
  • dotnetme,

     

    I am working with ASP 3.0.  I have a connection to my database (which is MS-Access) and it opens okay.  I am writing to tblReport okay as well.  I want to Query my database and then loop through to create a table that contains: Date, Printer Name, Problem Description.  Here is the code that I was using for my loop:

     

    Code Snippet

     

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head>
        <title>Printer Problem Report</title>
    </head>
    <body>

     

    <%
    Dim DataConn, rsDC

    ' Create and establish data connection
    Set DataConn = Server.CreateObject("ADODB.Connection")
    DataConn.ConnectionTimeout = 15
    DataConn.CommandTimeout = 30


    'Access connection code
    DataConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & Server.MapPath(".") & "\PrinterProblem.mdb;" & _
     "Persist Security Info=False"

    ' Create recordset and retrieve values using command object
    Set rsDC = Server.CreateObject("ADODB.Recordset")

     

    %>

    <table>
    <caption>Printer Problem Report</caption>
    <tr>
    <th>Date</th>
    <th>Printer</th>
    <th>Problem</th>
    </tr>


      
    <%

    With rsDC
     
     .Open "Select tblReport.Date, lkuPrinter.PrinterName, lkuProblem.ProblemDescription From tblReport Inner Join lkuPrinter ON tblReport.PrinterID = lkuPrinter.PrinterID Inner Join lkuProblem ON tblReport.ProblemID = lkuProblem.ProblemID", DataConn
     
     
     .MoveFirst

     'Loop to write data into Select
     Do While Not rsDC.EOF


      Response.Write "<tr>"
     Response.Write "<td>"
     Response.Write .Fields("Date") & "</td>"
     Response.Write "<td>"
     Response.Write .Fields("PrinterName") & "</td>"
     Response.Write "<td>"
        Response.Write .Fields("ProblemDescription") & "</td>"
        Response.Write "</tr>"

     rsDC.MoveNext
     Loop
     
    End With
    %>

    </table>

    <%
    ' Close Data Access Objects and free DB variables
     rsDC.Close
     Set rsDC =  Nothing
     DataConn.Close
     Set DataConn = Nothing
    %>

    </body>
    </html>

     

    Thanks again!

     

    Wednesday, May 16, 2007 7:38 PM
  •  

    dotnetme,

     

    By the way, I am still getting  a syntax error with the above code.

    Wednesday, May 16, 2007 7:42 PM
  • Well just guessing here but since Date is a reserved word it might be that...  try putting [] around Date..ie [DATE]..  NOT parentheses use Square Brackets...

    Code Snippet

    Select tblReport.[Date], lkuPrinter.PrinterName, lkuProblem.ProblemDescription
     From tblReport
     Inner Join lkuPrinter ON tblReport.PrinterID = lkuPrinter.PrinterID
     Inner Join lkuProblem ON tblReport.ProblemID = lkuProblem.ProblemID

     

    dotnetme
    Wednesday, May 16, 2007 8:16 PM
  • dotnetme,

     

    First of all, thanks for your help.  I'm all set now.  Here is what I did:

     

    Since the database was created with MS-Access I opened it up in Access and created a Query via the Wizard.  I was able to create the report that I want.  I then went to View SQL and got my Select statement.  Here it is:

     

    Code Snippet

     SELECT tblReport.Date, lkuPrinter.PrinterName, lkuProblem.ProblemDescription
    FROM lkuProblem INNER JOIN (lkuPrinter INNER JOIN tblReport ON lkuPrinter.PrinterID = tblReport.PrinterID) ON lkuProblem.ProblemID = tblReport.ProblemID

     

    Have a Great one!

    Thursday, May 17, 2007 1:29 PM
  • ultimate...

    thanks a lot dear...

    it helped me a lot...

    Monday, January 28, 2008 6:14 AM
  • Thanks alot..
    Thursday, November 22, 2012 7:33 AM