Complex Query Conundrum
-
- Posts: 2431
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Complex Query Conundrum
Always seem to struggle with some of these types of queries...
We have 3 BOs... Customer, Agency, Store
A Customer can be related to many Agency .... So, Customer has a Peer Many reference to Agency
A Store can be related to many Agency .... So, Store has a Peer Many reference to Agency
I know the Customer and I want a Query to display all of the Stores that are in an Agency that is related to that Customer.
I expected one of these to work... but no cigar...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency)>0
also tried...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN ThisStore.pm_Agency)>0
also tried...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN ThatStore.pm_Agency)>0
Any ideas or suggestions would be appreciated.
We have 3 BOs... Customer, Agency, Store
A Customer can be related to many Agency .... So, Customer has a Peer Many reference to Agency
A Store can be related to many Agency .... So, Store has a Peer Many reference to Agency
I know the Customer and I want a Query to display all of the Stores that are in an Agency that is related to that Customer.
I expected one of these to work... but no cigar...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency)>0
also tried...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN ThisStore.pm_Agency)>0
also tried...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN ThatStore.pm_Agency)>0
Any ideas or suggestions would be appreciated.
Last edited by customaware on Wed Oct 20, 2021 10:29 am, edited 1 time in total.
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....
-
- Posts: 1473
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Complex Query Conundrum
Code: Select all
Agency IN Agency IN
Agency cannot be in Agency
-
- Posts: 2431
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Complex Query Conundrum
It’s a typo in my post.
I’ll edit it.
I’ll edit it.
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....
-
- Posts: 1473
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Complex Query Conundrum
I tend to avoid IN and do the reverse relationship but it's not clear what the Agency.p_Store relationship.
If it's a ps then I would do
If they are many to many then I avoid using these in AIM and instead create an intermediary BO that handles the 1-m-1 relationship.
So CustomerAgent BO and StoreAgent BO
In part because that is what is happening anyway and in part because with lists you have little auditability - you can't track the date that a relationship became active and when it becomes inactive (without doing an audit log table)
If it's a ps then I would do
Code: Select all
FIND Store WHERE COUNT Agency WHERE ( Agency.psCustomer = Customer AND Agency.psStore = Store)>0
So CustomerAgent BO and StoreAgent BO
In part because that is what is happening anyway and in part because with lists you have little auditability - you can't track the date that a relationship became active and when it becomes inactive (without doing an audit log table)
-
- Posts: 2431
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Complex Query Conundrum
I don’t have a matching Agency.ps_Customer as each Agency could be associated with multiple Customers
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....
-
- Posts: 1473
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Complex Query Conundrum
I'm outta ideas then beyond the intermediary BO CustomerAgent.eagles9999 wrote: ↑Wed Oct 20, 2021 11:13 am I don’t have a matching Agency.ps_Customer as each Agency could be associated with multiple Customers
Agent.pmCustomerAgent
CustomerAgent.psAgent
CustomerAgent.psCustomer
Customer.pmCustomerAgent
Re: Complex Query Conundrum
I don't suppose you want to use a Stored Procedure to retrieve the records?
Bruce
Bruce
Re: Complex Query Conundrum
FIND Store WHERE (EXISTS Agency WHERE (Store IN Agency.pm_Stores AND Agency IN Customer.pm_Agency))
From,
Himanshu Jain
AwareIM Consultant (since version 4.0)
OS: Windows 10.0, Mac
DB: MYSQL, MSSQL
Himanshu Jain
AwareIM Consultant (since version 4.0)
OS: Windows 10.0, Mac
DB: MYSQL, MSSQL
-
- Posts: 2431
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Complex Query Conundrum
Thankyou all for your suggestions.....
This has turned out to be one of those situations where the actual data can trip you up.
In my original try....
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency)>0
It seemed incorrect because I was getting back ALL Stores which is not the answer I was expecting.
After frigging around with this for 2 days, I have now realised that one of the Agencies actually does have ALL Stores assigned to it. So the result was
in fact correct.....
When I limited the query to a particular Agency like this...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency AND Agency.ID=17)>0
Then it returns the correct number of Stores.
So, problem sorted.
Apologies for wasting everybody's time :-/
This has turned out to be one of those situations where the actual data can trip you up.
In my original try....
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency)>0
It seemed incorrect because I was getting back ALL Stores which is not the answer I was expecting.
After frigging around with this for 2 days, I have now realised that one of the Agencies actually does have ALL Stores assigned to it. So the result was
in fact correct.....
When I limited the query to a particular Agency like this...
FIND Store WHERE COUNT Agency WHERE ( Agency IN Customer.pm_Agency AND Agency IN Store.pm_Agency AND Agency.ID=17)>0
Then it returns the correct number of Stores.
So, problem sorted.
Apologies for wasting everybody's time :-/
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....