convert access query to mysql query 1

[mrsbean]

I am an Access guru who is attempting to better learn how to use MySQL. In Access, I had the following query:

SELECT phReqDocs.*, Query7.TransSubList, Query7.TransSubOrder
FROM phReqDocs LEFT JOIN Query7 ON phReqDocs.TransDoc = Query7.DocName;

Query7 code:

SELECT phTransDefaultDocs.*, phDocuments.DocName
FROM phTransDefaultDocs INNER JOIN phDocuments ON phTransDefaultDocs.AssignDoc=phDocuments.DocID;

Since I can't store "queries" in MySQL on the server, how can I fix the first query so that it selects the records in Query7 as a virtual table?

MrsBean

 

[r937]

just replace Query7 in the first query with the actual query in parentheses

SELECT phReqDocs.*
     , Query7.TransSubList
     , Query7.TransSubOrder
  FROM phReqDocs 
LEFT OUTER
  JOIN [COLOR=red](
       SELECT phTransDefaultDocs.*
            , phDocuments.DocName
         FROM phTransDefaultDocs 
       INNER 
         JOIN phDocuments 
           ON phTransDefaultDocs.AssignDoc
            = phDocuments.DocID
       ) as Query7[/color] 
    ON phReqDocs.TransDoc = Query7.DocName

p.s. you can do this in access too


r937.com | rudy.ca

 

[mrsbean]

I know it probably seems very much elementary to folks who learned databases in a different arena, but the syntax of how to create an actual query is something I still need to learn. Thanks for your help :0)

MrsBean

 

[r937]

don't apologize, i've used the microsoft access "Design View" to build queries, and it's really slick

i think there is a similar facility in the mysql query browser (available for free download on the mysql.com site), but i haven't tried it because i prefer writing the raw sql

r937.com | rudy.ca

 

[mrsbean]

Oops ... spoke too soon. There is an error below in the Line(s) that start with LEFT JOIN SELECT [ ... phTransactionType.TypeChecklist ... This query works in Access. It does not work in MySQL

SELECT phReqDocs.*, Query7.TransSubList, Query7.TransSubOrder, Query7.TypeChecklist AS TCL
FROM phReqDocs

LEFT JOIN [SELECT phTransDefaultDocs.*, phDocuments.DocName, phTransactionType.TypeChecklist

FROM (phTransDefaultDocs INNER JOIN phDocuments ON phTransDefaultDocs.AssignDoc = phDocuments.DocID) INNER JOIN phTransactionType ON phTransDefaultDocs.TransDocList = phTransactionType.TypeID;
       ]. AS Query7 ON phReqDocs.TransDoc = Query7.DocName
WHERE (((Query7.TransSubList)="A") AND ((Query7.TypeChecklist)="Residential"))
ORDER BY Query7.TransSubOrder;

All help is much appreciated.

MrsBean

 

[TonyGroves]

Well now, given that that is the only square bracket in your query, and all the other brackets are round ...

 

[r937]

microsoft access will "silently" convert the parentheses to square brackets (notice the period after the closing square bracket)

you have to change this back to parentheses to run in mysql

r937.com | rudy.ca

 

[mrsbean]

r937,

Thank you. I know I'm awful close, but it's still not clicking for me. In order to fix it, I tried removing the square brackets and replacing them with parentheses. I also removed the period. Now I have a query which doesn't return any results in MySQL but doesn't give me an error. It works in Access. Here is the latest version. Please help:

SELECT phReqDocs.*, Query7.TransSubList, Query7.TransSubOrder, Query7.TLC
FROM phReqDocs LEFT JOIN
(SELECT phTransDefaultDocs.*, phDocuments.DocName, phTransactionType.TypeChecklist AS TLC FROM (phTransDefaultDocs INNER JOIN phDocuments ON phTransDefaultDocs.AssignDoc = phDocuments.DocID) INNER JOIN phTransactionType ON phTransDefaultDocs.TransDocList = phTransactionType.TypeID )
AS Query7 ON phReqDocs.TransDoc = Query7.DocName WHERE (((Query7.TransSubList)='A') AND ((Query7.TLC)='Residential')) ORDER BY Query7.TransSubOrder ;

MrsBean

 

[r937]

you've added a WHERE clause which wasn't there earlier (i would have mentioned this before but i was distraced by the square brackets)

there are two ways to incorporate these conditions

either write them into the subquery --

SELECT phReqDocs.*
     , Query7.TransSubList
     , Query7.TransSubOrder
  FROM phReqDocs 
LEFT OUTER
  JOIN (
       SELECT phTransDefaultDocs.*
            , phDocuments.DocName
         FROM phTransDefaultDocs 
       INNER 
         JOIN phDocuments 
           ON phTransDefaultDocs.AssignDoc
            = phDocuments.DocID
        [COLOR=blue]WHERE phTransDefaultDocs.TransSubList='A'
          AND phTransDefaultDocs.TLC='Residential'[/color]            
       ) as Query7 
    ON phReqDocs.TransDoc = Query7.DocName

or else in the join clause, i.e. not the WHERE clause --

SELECT phReqDocs.*
     , Query7.TransSubList
     , Query7.TransSubOrder
  FROM phReqDocs 
LEFT OUTER
  JOIN (
       SELECT phTransDefaultDocs.*
            , phDocuments.DocName
         FROM phTransDefaultDocs 
       INNER 
         JOIN phDocuments 
           ON phTransDefaultDocs.AssignDoc
            = phDocuments.DocID        
       ) as Query7 
    ON phReqDocs.TransDoc = Query7.DocName
   [COLOR=blue]AND Query7.TransSubList='A'
   AND Query7.TLC='Residential'[/color]

r937.com | rudy.ca