Hi Guys,
We have 2 tables for instance Properties & Tenants. A Property can have multiple Tenants And a tenant Can Be Linked To Multiple Properties. However I want to be able to display a Query that would show all the Tenants next to each one of the properties they are linked to.
We are able to write a nice SQL inner join that displays the correct results in MySQL CLI but when I try to use that query in in Aware it Says 'Cannot Find business Object property_ref'. We obviously need to use the Ref table to get what we want but since that is not an object in AwareIM I have no way to display that data set.
Is this a known Aware limitation? What is the point of having access to 'SQL Form' of the Query writer if you cannot display results of any SQL query that is not a direct Aware Object?
Here is the Query we run in the CLI which gives the exact result set we want to see:
SELECT bastestdomaincustomquerytest_tenant.TenantName AS Tenant, bastestdomaincustomquerytest_property.PropertyName AS Property FROM bastestdomaincustomquerytest_property_ref INNER JOIN bastestdomaincustomquerytest_tenant ON basdbtest.bastestdomaincustomquerytest_property_ref.RID=bastestdomaincustomquerytest_tenant.ID INNER JOIN bastestdomaincustomquerytest_property ON basdbtest.bastestdomaincustomquerytest_property_ref.ID=bastestdomaincustomquerytest_property.ID
+--------+------------+
| Tenant | Property |
+--------+------------+
| Johan | Chablis 10 |
| Barend | Chablis 10 |
| James | Chablis 10 |
| Nadia | Wylant 20 |
| Simon | Wylant 20 |
| Rick | Wylant 20 |
| Johan | Brink 50 |
| Barend | Brink 50 |
| Nadia | Brink 50 |
| Simon | Brink 50 |
| Rick | Brink 50 |
| James | Brink 50 |
| Johan | Smith 30 |
| Barend | Smith 30 |
| Simon | Smith 30 |
+--------+------------+
If this is a known limitation... Then Support, is this something you would ever be able to address?
Thnx in advance for any input
INNER JOIN - Multi to Multi Support
-
- Posts: 345
- Joined: Mon Nov 12, 2012 9:08 pm
- Location: South Africa
INNER JOIN - Multi to Multi Support
Hein Hanekom & Werner Hanekom
Sinov8.net
AwareIM Version 5.9 | 6.0 | 7.0 | 7.1 (Windows EC2 R2012 & MySQL)
Sinov8.net
AwareIM Version 5.9 | 6.0 | 7.0 | 7.1 (Windows EC2 R2012 & MySQL)
Re: INNER JOIN - Multi to Multi Support
Isn't a Property/Tenant(s) scenario just like an Order/Order Line(s) scenario (where Lines contain an Attribute for the Product), and thus Lines are 'carriers' for Products?
An Order can have multiple Lines and thus Orders are associated with multiple Products , and Products can be in Lines of multiple Orders.
A Property can have one or more Tenant 'lines', and each Line contains an attribute for the Tenant itself, rather than the Tenant being a direct Reference Attribute of a Property.
I know you are far more experienced than I am, and the above is pretty basic, but it's easy to generate a listing of Products next to the Orders they appear in.
Thus it's easy to generate a listing of Tenants and the Properties they appear in.
An Order can have multiple Lines and thus Orders are associated with multiple Products , and Products can be in Lines of multiple Orders.
A Property can have one or more Tenant 'lines', and each Line contains an attribute for the Tenant itself, rather than the Tenant being a direct Reference Attribute of a Property.
I know you are far more experienced than I am, and the above is pretty basic, but it's easy to generate a listing of Products next to the Orders they appear in.
Thus it's easy to generate a listing of Tenants and the Properties they appear in.
-
- Posts: 2418
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: INNER JOIN - Multi to Multi Support
Hein,
Not sure if this will help you but might be easier...
Create a Cross Reference BO for Property and Tenant using the Other literal.
I do this a lot for creating Currency Exchange Cross Rates....
Here is a doc on how to implement.....
Not sure if this will help you but might be easier...
Create a Cross Reference BO for Property and Tenant using the Other literal.
I do this a lot for creating Currency Exchange Cross Rates....
Here is a doc on how to implement.....
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....