[djadema]

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.

[Bunce]

OK, lets format it so we can understand it:

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:

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

[djadema]

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.

[Bunce]

Post your ASP code, and the result of the response.write command I listed.

[djadema]

Allright, here goes:

 <%
'-----------------------------------------------
'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;

[WarrenRoscoe]

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 ;

[djadema]

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:

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:

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

[WarrenRoscoe]

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.