Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Post Unanswered: Multiple (inner or outer) Access SQL joins

    Hi,

    I've been trying to merge the results of three tables, but I keep getting the following error message:


    Microsoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression 'Products.P_ID = VerJoin.V_Product INNER JOIN Prodtype ON Products.P_Prodtype = Prodtype.PT_ID'.

    I was asked to get this asp-page to display the results in date order, instead of in alphabetical order. Because the required date-field, V_Date is located in a different table called Versions, I thought I could just join it together with the other two tables Products and Prodtype. The V_Product-field in Versions and Products.P_ID can be used to make the join.


    Here's my SQL query:

    SELECT * FROM Products LEFT OUTER JOIN (SELECT * FROM Versions WHERE V_Product = " & prodtype & ") AS VerJoin ON Products.P_ID = VerJoin.V_Product INNER JOIN Prodtype ON Products.P_Prodtype = Prodtype.PT_ID WHERE (((Products.P_Prodtype)=" & prodtype & ")) ORDER BY VerJoin.V_Date, Products.P_Name ;


    What am I doing wrong?


    Thanks in advance,


    Dj.

  2. #2
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    OK, lets format it so we can understand it:

    Code:
    SELECT 
       * 
    FROM 
       Products 
    LEFT OUTER JOIN 
       (
       SELECT 
          * 
       FROM 
          Versions 
       WHERE 
          V_Product = " & prodtype & "
        ) AS VerJoin 
    ON 
       Products.P_ID = VerJoin.V_Product 
    INNER JOIN 
       Prodtype 
    ON 
       Products.P_Prodtype = Prodtype.PT_ID 
    WHERE 
       Products.P_Prodtype = " & prodtype & "
    ORDER BY 
       VerJoin.V_Date
       , Products.P_Name
    Ahh, thats better.

    Now, remove the AS from AS Verjoin. Its a table alias, not a column alias. (Might still work but not the way I learnt SQL)

    Secondly, I'm assuming the variable prodtype is a string and should therefore be surrounded by single quotes.

    Thirdly, since you're using ASP, response.write out the string to the page, just before you send it to the database.. That way you can see exactly what is being sent to the database, including the variable values.

    Here's an updated SQL statement, given a variable in ASP of strSQL:
    Code:
    strSQL = "
    SELECT 
       * 
    FROM 
       Products 
    LEFT OUTER JOIN 
       (
       SELECT 
          * 
       FROM 
          Versions 
       WHERE 
          V_Product = '" & prodtype & "'
        ) VerJoin 
    ON 
       Products.P_ID = VerJoin.V_Product 
    INNER JOIN 
       Prodtype 
    ON 
       Products.P_Prodtype = Prodtype.PT_ID 
    WHERE 
       Products.P_Prodtype = '" & prodtype & "'
    ORDER BY 
       VerJoin.V_Date
       , Products.P_Name"
    
    Response.Write (strSQL)
    Response.End
    Last edited by Bunce; 02-27-03 at 06:56.
    There have been many posts made throughout the world.
    This was one of them.

  3. #3
    Join Date
    Feb 2003
    Posts
    4
    Hi Bunce, thanks for your quick reply,


    I copied and pasted your modified query into my script, but unfortunately it didn't solve the problem.
    Any suggestions?


    Thank you,


    dj.

  4. #4
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Post your ASP code, and the result of the response.write command I listed.
    There have been many posts made throughout the world.
    This was one of them.

  5. #5
    Join Date
    Feb 2003
    Posts
    4
    Allright, here goes:


    Code:
     <%
    '-----------------------------------------------
    'PULL OUT PRODUCTS/PRODTYPES FROM DATABASE		
    '-----------------------------------------------
    theQuery2 = "SELECT * FROM Products LEFT OUTER JOIN (SELECT * FROM Versions WHERE V_Product = '" & prodtype & "') VerJoin ON Products.P_ID = VerJoin.V_Product INNER JOIN Prodtype ON Products.P_Prodtype = Prodtype.PT_ID WHERE Products.P_Prodtype = '" & prodtype & "' ORDER BY VerJoin.V_Date,Products.P_Name;"
    theQuery = "SELECT * FROM Products INNER JOIN Prodtype ON Products.P_Prodtype = Prodtype.PT_ID WHERE (((Products.P_Prodtype)=" & prodtype & ")) ORDER BY Products.P_Name ;"
    cmdTemp.CommandText = theQuery
        cmdTemp.CommandType = 1
        Set cmdTemp.ActiveConnection = DataConn
        rs_main.Open cmdTemp, , 1, 3
    IntNumRecs = rs_main.RecordCount
    If IntNumRecs=0 then
    %>
    <font face="verdana" size="2"><b>OP DIT MOMENT ZIJN HIER GEEN ITEMS BESCHIKBAAR</b></font>
    <% else %>
    <font face="verdana" size="4" ><font face="verdana" size="4" ><b>
    <% 
    varProduct =  rs_main("P_Prodtype")
    Select Case True
    Case varProduct  > 10 AND varProduct < 15
    			Response.Write ""
    
    Case varProduct  > 0 AND varProduct < 10 
    			Response.Write "Boeken met Samenvatting"
    
    Case varProduct  = 29 
    			Response.Write "Boeken met Samenvatting"
    
    Case varProduct  = 35 
    			Response.Write "Boeken met Samenvatting"
    
    Case varProduct  > 14 AND varProduct < 26
    			Response.Write ""
    
    End Select
    %>
    
    </b></font><br>
    <font face="verdana" size="2"><b><% response.write(rs_main("PT_Name")) %></b></font><hr size="1" color="#C0C0C0">
        <% response.write(rs_main("PT_Desc")) %></font><br>
        <hr size="1" color="#C0C0C0">
    <table border="0" cellpadding="0" cellspacing="0" width="450">
    <%
    '-----------------------------------------------
    'COUNT THROUGH FIRST FEW RECORDS IF NOT PAGE 1	
    'OK, it's bodge but stops us having to hold		
    'recordsets in sessions.						
    '-----------------------------------------------
    If recor<>1 then
    	do until CInt(recordnumber)=CInt(recor-1)
    		recordnumber = recordnumber + 1
    		rs_main.movenext
    	loop
    end if
    do while recordnumber<(recor+prodtypepagesize-1) AND not rs_main.EOF
    recordnumber = recordnumber + 1 %>
    <tr>
    	<td><font face="verdana" size="2"><b><% =recordnumber %>.&nbsp;<a href="product.asp?product=<% response.write(rs_main("P_ID")) %>"><% response.write(rs_main("P_Name")) %></a></b></font></td>
    </tr>
    <tr>
    	<td height="140" valign="top"><font face="verdana" size="2">
    	<% if rs_main("P_Image") <>"" then %><a href="product.asp?product=<% response.write(rs_main("P_ID")) %>">
    	<img src="../productpics/<% response.write(rs_main("P_Image")) %>" border="0" hspace="10" vspace="10" align="left"></a>
    
    	<% end if %>
    	<% response.write(rs_main("P_Comment")) %> <br>
    <br>
    <a href="product.asp?product=<% response.write(rs_main("P_ID")) %>">
    <% 
    strProduct =  rs_main("P_Prodtype") 
    
    If strProduct  >0 AND strProduct <10 OR strProduct =35 OR strProduct =29 Then
    			Response.Write "<img src=../images_algemeen/bestel.jpg width=139 height=25 border=0 align=right >"
    Else
    			Response.Write "<img src=../images_algemeen/inschrijven.gif width=222 height=25 border=0 align=right>"
    End If
    %>
    </a></font><br>
    	<img src="../images_webshop/spacer.gif" width="10" height="35" ></td>
    </tr>
    <%
    rs_main.moveNext
    loop
    rs_main.Close
    DataConn.Close
    set DataConn = nothing
    
    '-----------------------------------------------
    'THIS SECTION GENERATES PREV/NEXT AND PAGE NOS.	
    'Looks worse that it is. Just decides whether or
    'not a 'next' or 'previous' button is needed,	
    'whether any page numbers are needed and then	
    'displays them. It will black out the page you	
    'are on so you can recall it, just like a big	
    'boy's search engine does.						
    '-----------------------------------------------
    %>
    <%= theQuery2 %>
    produces among other things:

    SELECT * FROM Products LEFT OUTER JOIN (SELECT * FROM Versions WHERE V_Product = '15') VerJoin ON Products.P_ID = VerJoin.V_Product INNER JOIN Prodtype ON Products.P_Prodtype = Prodtype.PT_ID WHERE Products.P_Prodtype = '15' ORDER BY VerJoin.V_Date,Products.P_Name;
    Last edited by djadema; 02-27-03 at 10:29.

  6. #6
    Join Date
    Feb 2003
    Posts
    4
    Your'e missing one set of brackets which Access seems to love ... and I hate!

    Your original query is ...
    SELECT * FROM Products LEFT OUTER JOIN (SELECT * FROM Versions WHERE V_Product = " & prodtype & ") AS VerJoin ON Products.P_ID = VerJoin.V_Product INNER JOIN Prodtype ON Products.P_Prodtype = Prodtype.PT_ID WHERE (((Products.P_Prodtype)=" & prodtype & ")) ORDER BY VerJoin.V_Date, Products.P_Name ;

    Add brackets
    SELECT * FROM
    (
    Products LEFT OUTER JOIN (SELECT * FROM Versions WHERE V_Product = " & prodtype & ") AS VerJoin ON Products.P_ID = VerJoin.V_Product
    )
    INNER JOIN Prodtype ON Products.P_Prodtype = Prodtype.PT_ID WHERE (((Products.P_Prodtype)=" & prodtype & ")) ORDER BY VerJoin.V_Date, Products.P_Name ;

    Or alternately, use the following syntax

    SELECT
    *
    FROM
    (
    Products
    LEFT OUTER JOIN Versions ON Products.P_ID = Versions.V_Product
    )
    INNER JOIN Prodtype ON Products.P_Prodtype = Prodtype.PT_ID WHERE
    (((Products.P_Prodtype)=" & prodtype & "))
    ORDER BY Versions.V_Date, Products.P_Name ;

  7. #7
    Join Date
    Feb 2003
    Posts
    4
    Thanks Warren,


    But I already found the right query, it just needs to be ordered by date from the earliest to the latest.
    Maybe you can help me with this? (can I use MAX and if so, where should I put it?)
    I also need to group my results, because some products are presented twice.
    I tried using GROUP BY Products.P_Name, but that gave me an error:

    Code:
    Microsoft JET Database Engine error '80040e21' 
    
    Cannot group on fields selected with '*'. 
    
    /www/pages/pages_middle/asp/prodtype.asp, line 48
    Why is this? I've never had this problem before with MySQL...


    Here's my new query:

    Code:
    SELECT * FROM ( Products INNER JOIN ProdType ON (ProdType.PT_ID = Products.P_ProdType) ) LEFT OUTER JOIN Versions ON (Products.P_ID = Versions.V_Product) WHERE Products.P_Prodtype = " & prodtype & " GROUP BY Products.P_Name ORDER BY Versions.V_Date;
    Thanks again,

    dj

  8. #8
    Join Date
    Feb 2003
    Posts
    4
    To order by date ...

    SELECT a,b,c, datefield FROM y WHERE z ORDER BY datefield

    To select unique values, use SELECT DISTINCT

    In general if you want to GROUP, you have to do something like the following ...

    SELECT
    a
    ,b
    ,c
    ,MAX(d)
    FROM
    table
    GROUP BY a, b, c

    Using SELECT * is not advisable as it is bad practice, and if the fields in your table(s) change, you will have to review the statement to add additional fields in the group by clause.

    Hope this helps ... it is a bit difficult when the table definitions are unknown.

Posting Permissions

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