CODEWITHSUNDEEP
mysqlms-access
smartins
smartins

Reputation: 3888

mysql equivalent to access stored queries

I'm converting a web site that uses access as it's main database to MySQL. I've encountered an issue with a SQL statement that I've haven't been able to found an answer via google.

This SQL statement references a stored query (qryProgramMostRecentVersion):

strSQL1 = "SELECT q.Program_ID, q.ProgramName, q.MostRecentVersion, q.MostRecentVersionID " & _
"FROM qryProgramMostRecentVersion AS q WHERE NOT EXISTS(" & _
"SELECT OrderID FROM Orders " & _
"WHERE q.MostRecentVersionID = Orders.ProgramVersion_ID AND " & _
"Orders.Customer_ID = " & strCustomerID & " AND Orders.RegStatus_ID=1) " & _
"ORDER BY q.Program_ID"

Is there any equivalent in MySQL?

Upvotes: 1

Views: 835

Answers (2)

Chandu
Chandu

Reputation: 82903

You can use View. Try something like this: --Create a VIew in the database

CREATE OR REPLACE VIEW vwPrgRecentVersion AS
SELECT q.program_id, 
       q.programname, 
       q.mostrecentversion, 
       q.mostrecentversionid 
FROM   qryprogrammostrecentversion AS q 
WHERE  NOT EXISTS(SELECT orderid 
                  FROM   orders 
                  WHERE  q.mostrecentversionid = orders.programversion_id 
                         AND orders.regstatus_id = 1) ;

and then from your code use this: 

SELECT *
  FROM vwPrgRecentVersion
 WHERE Orders.Customer_ID =  " & strCustomerID & " ORDER BY Program_ID"

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60498

Yes, it's called a View. Just use CREATE VIEW <viewname> AS followed by your query:

CREATE VIEW qryProgramMostRecentVersion AS
SELECT col1, col2 FROM sometable
....

Upvotes: 4

Related Questions