HI MySQL does not support sub selects/nested queries :(
Peter -----Original Message----- From: Eric Leupold [mailto:[EMAIL PROTECTED] Sent: 29 March 2003 16:43 To: [EMAIL PROTECTED] Subject: Nested SQL QUERY I hope someone can help me with a syntax error I'm getting with a nested query. I'm a newbie. Here is the statement: SELECT * FROM tblmarkets WHERE MarketID IN (SELECT MarketID FROM tblmarketproducts, tblproducts WHERE tblmarketproducts.ProductID = tblproducts.ProductID AND ProductName = 'Apples') ORDER BY MarketName and I'm getting the following error message: "You have an error in your SQL syntax near 'SELECT MarketID FROM tblmarketproducts, tblproducts WHERE tblmarke' at line 4" I have 3 tables - tblmarkets, tblmarketproducts, and tblproducts. I am trying to return the columns in tblmarkets based on the value of 'Apples' (test value for a form variable). MarketID is the primary key in tblmarkets which is related to MarketID in tblmarketproducts. The second column in tblmarketproducts is ProductID which is related to ProductID in tblproducts. The other column in tblproducts is ProductName which is the initial form variable the query is based upon ('Apples' is the test variable for the statement'). I have also tried a nested INNER JOIN statement: SELECT tblMarkets.*, tblMarketProducts.*, tblProducts.ProductName FROM tblProducts INNER JOIN (tblMarkets INNER JOIN tblMarketProducts ON tblMarkets.MarketID = tblMarketProducts.MarketID) ON tblProducts.ProductID = tblMarketProducts.ProductID WHERE tblMarkets.ProductID = varProductID and MarketName LIKE 'varName%' AND City Like 'varCity%' AND State LIKE 'varState%' AND County LIKE 'varCounty%' AND Zip LIKE 'varZip%' AND Approved = '1' ORDER BY MarketName and get a syntax error in line 2. I am at a loss and would ve grateful for any help. Eric Leupold -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
