Nested SQL statements are not supported except in insert statements in
MySQL (4.1 may include this feature, if not seems scheduled to be
included soon). You can accomplish this query using an inner join.
SELECT m.*
FROM tblmarkets m
INNER JOIN tblmarketproducts mp ON mp.MarketID = m.MarketID
INNER JOIN tblproducts p ON mp.ProductID = p.ProductID
WHERE p.ProductName='Apples';
I used the following database setup to test this:
DROP TABLE IF EXISTS tblmarkets;
CREATE TABLE IF NOT EXISTS tblmarkets (
MarketID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
MarketName VARCHAR(200)
);
DROP TABLE IF EXISTS tblmarketproducts;
CREATE TABLE IF NOT EXISTS tblmarketproducts (
ProductID INT UNSIGNED NOT NULL,
MarketID INT UNSIGNED NOT NULL,
PRIMARY KEY (MarketID, ProductID),
INDEX (ProductID)
);
DROP TABLE IF EXISTS tblproducts;
CREATE TABLE IF NOT EXISTS tblproducts (
ProductID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(200) NOT NULL,
INDEX (ProductName)
);
INSERT INTO tblmarkets VALUES (NULL, "Market1");
INSERT INTO tblmarkets VALUES (NULL, "Market2");
INSERT INTO tblmarkets VALUES (NULL, "Market3");
INSERT INTO tblproducts VALUES(NULL, "Cherries");
INSERT INTO tblproducts VALUES(NULL, "Apples");
INSERT INTO tblproducts VALUES(NULL, "Pears");
INSERT INTO tblproducts VALUES(NULL, "Appricots");
INSERT INTO tblmarketproducts VALUES(1, 1);
INSERT INTO tblmarketproducts VALUES(1, 3);
INSERT INTO tblmarketproducts VALUES(2, 2);
INSERT INTO tblmarketproducts VALUES(2, 3);
INSERT INTO tblmarketproducts VALUES(4, 2);
commit;
~Wynne Crisman
-----Original Message-----
From: Eric Leupold [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 29, 2003 7:43 AM
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]
