Added INNER/LEFT/RIGHT JOIN, GROUP BY and HAVING.

[elmicha]

Hi,

I had a look at the join implementation of mdb-queries, and added INNER/LEFT/RIGHT JOIN, GROUP BY and HAVING. It works for very simple queries, but not for more complicated ones.

Best regards...

[evanmiller]

Thanks for the contribution. Is the saw_case_5 stuff necessary?

[elmicha]

Yes, that's the part where I'm still struggling with. I now put the name1 inside the comment for case 7b2, which seems to make more sense. I have a few test files here, but alas I cannot share them, because they come from my workplace.

I left the saw_case_5 and these 7a and 7b2 comments in there in the hope that they encourage users to have a look. Maybe someone has a good idea how to fix this, and make it work with nested left/right joins.

[pedromorgan]

I need to create some test databases.. and real word kinda stuff within...

So we can fling stuff at them.. Its in another repos right ??

[evanmiller]

@elmicha Explanatory code comments are fine, but I'd prefer to leave any mysteries (/* 7a */ etc) out of the generated SQL.

[elmicha]

I would love to remove these markers and the stderr messages, but I think the code is not yet ready for prime time, so I think it would be better if we leave these markers in at the moment, so we have a chance to understand what's going on.

I added a script mdb-export-all that runs mdb-queries on all views in a file. In MS Access there are templates for databases, and I tried it with the German version of the Northwind template. The English version can be downloaded here. Alas it's only a template, not a real database. It must be opened in Access and only then an accdb is created and can be used with mdb-queries.

The [Invoice Data] view is shown by mdb-queries like this (the SELECT is ok, I omit it here):

SELECT ...
  FROM  /*7a*/ Orders
  LEFT JOIN Employees Extended
    ON Orders.[Employee ID] = [Employees Extended].ID /*7b Customers */ 
 RIGHT JOIN Orders
    ON Customers.ID = Orders.[Customer ID] /*7b Shippers */ 
 RIGHT JOIN Orders
    ON Shippers.ID = Orders.[Shipper ID] /*7b Order Details */ 
  LEFT JOIN Products
    ON [Order Details].[Product ID] = Products.ID /*7b Orders */ 
  LEFT JOIN Order Details
    ON Orders.[Order ID] = [Order Details].[Order ID]

We can see that there is no Shippers anywhere in the FROM or JOIN tables, but it is used in the ON clauses. That's clearly wrong.

MS Access shows this SQL (I added the line feeds):

SELECT ...
 FROM (Shippers 
RIGHT JOIN (Customers 
RIGHT JOIN (Orders 
 LEFT JOIN [Employees Extended] ON Orders.[Employee ID] = [Employees Extended].ID) 
   ON Customers.ID = Orders.[Customer ID]) 
   ON Shippers.ID = Orders.[Shipper ID]) 
 LEFT JOIN ([Order Details] 
 LEFT JOIN Products 
   ON [Order Details].[Product ID] = Products.ID) 
   ON Orders.[Order ID] = [Order Details].[Order ID];

Note that there are a lot of parentheses, and the order of the LEFT/RIGHT is different, and also the table names are different.

I have no idea how to get this right.

The view [~sq_cHome~sq_csbfSalesPivot] (that name is also strange) shows more mysteries:

SELECT DISTINCTROW Employees.[Country/Region],Employees.[Last Name],Employees.[First Name],Orders.[Shipped Date],Orders.[Order ID],[Order Subtotals].Subtotal
  FROM  /*7b Orders */
 INNER JOIN Order Subtotals
    ON Orders.[Order ID]=[Order Subtotals].[Order ID] /*7b Employees */
 INNER JOIN Orders
    ON Employees.ID=Orders.[Employee ID], /*5b*/ Employees, /*5b*/ Orders, /*5b*/ Order Subtotals
 WHERE (((Year([Shipped Date]))=Year(Date())) AND ((DatePart("q",[Shipped Date]))=DatePart("q",Date())))

There's the FROM table missing, and the last ON has a few tables (marked with /5b/) added at the end. That's also very broken.

So I don't know how to continue with this.

[evanmiller]

@elmicha Thanks for the additional information. This is somewhat too complicated for me to work on myself, but I will leave this open in case you or others are able to make any progress on it.