Filter Parent Based on Child Table

[Scott24x7]

Hi All,
I have a problem in that I want to filter a parent table based on a child table. So the scenario is:

Parent table: Company - Key - CompanyID
Child table: Contact - Key - CompanyID
Field to filter on in Contact - Introduction

So what I want to do is fine all the companies that had contacts introduction based on that field. So we may have values of introduction as:
World Expo
Internet Search
Industry Networking Event

Then I want to pick, say "World Expo" from dropdown (which I've created), and what it will do is show me only companies that have a contact that was made at "World Expo".

I'm not sure how to go about matching that, and setting a filter condition. Any suggestions?


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

[tt]CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS[/tt]

Is that really the condition you want to join on? Earlier you said

companies that had contacts introduction based on that field

With that field being the introduction key value.

Are you listening to yourself here, when you make a join strarting from COMPANY to CTSTATUS instead of joining from CONTACT to CTSTATUS?
You said you have several contacts, and one of them could be "WORLD EXPO", that wouldn't show up in company data, would it? It would show up in each contact record, wouldn't it?

If not, it seems you have your data structured wrong.

Bye, Olaf.

 

[Scott24x7]

Olaf,
This code:

	SELECT COMPANY.COMPANYID, COMPANY.COMPANYNAME, COMPANY.COMPANYCOUNTRY, COMPANY.COMPANYINDUSTRYTYPE,;
		CTSTATUS.COMPANYSTATUSDESCRIP, COMPANY.COMPANYDCRELATED, COMPANY.COMPANYCLIENTSTATUS, .F. AS LEDGERCONTROL;
		FROM COMPANY;
		LEFT JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS;
		INTO CURSOR COMPANYGRID READWRITE ORDER BY COMPANYNAME

Is what I use to populate the grid with.
When there are conditions that ONLY deal with the COMPANY table, I simply issue things like:
SET FILTER TO <condition>

But because the WHEREMET field is not in the COMPANY table, but only in the CONTACT table, I wanted to populate the grid CURSOR (COMPANYGRID) using the select statement that just gave me companies based on contacts that match the value in the dropdown "filter" selection. (It doesn't have to be a filter if the result appears to be one, as it's just a grid for selection control right?) So, I was trying to build on the existing SELECT to give me a different result set, but the LEFT JOIN is necessary to change numeric values 1 - 8 into meaningful values like "Active Client, Out of Business, Vendor, Competitor" (there are 8 possible expressions). So the code table contains 1 - 8 and their expression, the JOIN takes the text value from CTSTATUS and populates the CURSOR with the text value instead of a numeric one so that it's easier for the user to understand. So that all works fine.

I was using it as the basis then to try to expand on using a SELECT as you suggested to populate my cursor. So everything from before still needs to happen, but I only want companies now that match the dropdown selected for Introduction (Filter by Introduction). That is where the CONTACT table comes in, and it has the value from the dropdown filter selection. The only relationship then from CONTACT to COMPANY is COMPANYID. So I just want a list of COMPANYS that have a contact that matches the filter condition from the drop down...

For that I tried the code, based on your descriptions:

	SELECT COMPANY.COMPANYID, COMPANY.COMPANYNAME, COMPANY.COMPANYCOUNTRY, COMPANY.COMPANYINDUSTRYTYPE,;
		CTSTATUS.COMPANYSTATUSDESCRIP, COMPANY.COMPANYDCRELATED, COMPANY.COMPANYCLIENTSTATUS, .F. AS LEDGERCONTROL;
		FROM COMPANY;
		INNER JOIN CONTACT ON COMPANY.COMPANYID = CONTACT.COMPANYID;
		LEFT JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS;
			WHERE ALLTRIM(CONTACT.CONTACTWHEREMET) = ALLTRIM(This.Parent.cboFilterIntroduction.VALUE);
		INTO CURSOR COMPANYGRID READWRITE ORDER BY COMPANYNAME

But it just returns an empty CURSOR.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

>the LEFT JOIN is necessary to change numeric values 1 - 8 into meaningful values like "Active Client, Out of Business, Vendor, Competitor"
Wrong, a join is necessary, but not a LEFT join.

An INNER join also "converts" the number (ID, foreign key, starus type number) to the status by joining CTSTATUS. I don't know what wrong ideas you have about joins, but you do. A join or a lookup or a relation doesn't convert data, it joins data matching that status number with the correct row of the status table.

Are you saying this is giving you records...

SELECT COMPANY.COMPANYID, COMPANY.COMPANYNAME, COMPANY.COMPANYCOUNTRY, COMPANY.COMPANYINDUSTRYTYPE,;
		CTSTATUS.COMPANYSTATUSDESCRIP, COMPANY.COMPANYDCRELATED, COMPANY.COMPANYCLIENTSTATUS, .F. AS LEDGERCONTROL;
		FROM COMPANY;
		LEFT JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS;
		INTO CURSOR COMPANYGRID READWRITE ORDER BY COMPANYNAME

...while this is not giving any records...

SELECT COMPANY.COMPANYID, COMPANY.COMPANYNAME, COMPANY.COMPANYCOUNTRY, COMPANY.COMPANYINDUSTRYTYPE,;
		CTSTATUS.COMPANYSTATUSDESCRIP, COMPANY.COMPANYDCRELATED, COMPANY.COMPANYCLIENTSTATUS, .F. AS LEDGERCONTROL;
		FROM COMPANY;
		INNER JOIN CONTACT ON COMPANY.COMPANYID = CONTACT.COMPANYID;
		LEFT JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS;
			WHERE ALLTRIM(CONTACT.CONTACTWHEREMET) = ALLTRIM(This.Parent.cboFilterIntroduction.VALUE);
		INTO CURSOR COMPANYGRID READWRITE ORDER BY COMPANYNAME

?

 

[Olaf Doschke]

SELECT *;
		FROM COMPANY;
		[b]INNER[/b] JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS

Is that giving any results?

Bye, Olaf.

 

[Tore Bleken]

Scott,

the Where clause comes LAST in the Select expression!

 

[Scott24x7]

Olaf,
Yes, in the message above with two selects, the first one gives me exactly what I need for that criteria. Interesting you noted that LEFT is not required. I will try it without, as that will make sense to me. Oh, I see it now in the first one. COMPANYCLIENTSTATUSDESCRIP is SELECTED and JOIN(ed) in the "JOIN" clause where the CCOMPANYCLIENTSTATUS matches to the COMPANYSTATUSTYPE. Might be getting clearer now.

Tore,
Oh, the WHERE should be after the INTO? That seems random...


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Adding to the working query step by step:

First only join CONTACT:

SELECT COMPANY.COMPANYID, COMPANY.COMPANYNAME, COMPANY.COMPANYCOUNTRY, COMPANY.COMPANYINDUSTRYTYPE,;
		CTSTATUS.COMPANYSTATUSDESCRIP, COMPANY.COMPANYDCRELATED, COMPANY.COMPANYCLIENTSTATUS, .F. AS LEDGERCONTROL,;
                CONTACT.CONTACTWHEREMET;
		FROM COMPANY;
		INNER JOIN CONTACT ON COMPANY.COMPANYID = CONTACT.COMPANYID;
		INNER JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS;
		INTO CURSOR COMPANYGRID READWRITE ORDER BY COMPANYNAME

No where clause. You still see data?

Bye, Olaf.

 

[Scott24x7]

So I removed the LEFT statement from the first example, and it still yielded the same result (good result), so I guess I was getting lucky with it before. SQL is just not my strong suit, though I'm trying to use it. The syntax seems very powerful, but very confusing once I get more than 1 table involved. And the lower statement is 3 tables.

I made a change, the full idea (it's in an IF statement, so depending on this condition it builds the cursor differently:

IF This.Parent.cboFilterIntroduction.Value = ""
	SELECT COMPANY.COMPANYID, COMPANY.COMPANYNAME, COMPANY.COMPANYCOUNTRY, COMPANY.COMPANYINDUSTRYTYPE,;
		CTSTATUS.COMPANYSTATUSDESCRIP, COMPANY.COMPANYDCRELATED, COMPANY.COMPANYCLIENTSTATUS, .F. AS LEDGERCONTROL;
		FROM COMPANY;
		JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS;
		INTO CURSOR COMPANYGRID READWRITE ORDER BY COMPANYNAME
ELSE
	SELECT COMPANY.COMPANYID, COMPANY.COMPANYNAME, COMPANY.COMPANYCOUNTRY, COMPANY.COMPANYINDUSTRYTYPE,;
		CTSTATUS.COMPANYSTATUSDESCRIP, COMPANY.COMPANYDCRELATED, COMPANY.COMPANYCLIENTSTATUS, CONTACT.CONTACTWHEREMET, .F. AS LEDGERCONTROL;
		FROM COMPANY;
		INNER JOIN CONTACT ON CONTACT.COMPANYID = COMPANY.COMPANYID;
		JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS;
 			WHERE ALLTRIM(CONTACT.CONTACTWHEREMET) = ALLTRIM(This.Parent.cboFilterIntroduction.VALUE);
		INTO CURSOR COMPANYGRID READWRITE ORDER BY COMPANYNAME
ENDIF

The top one works great, and even "recovers" after the ELSE clause leaves a blank CURSOR. (In other words, I pick another condition to filter that isn't an INTRODUCTION type.)
Or I just say show me all. The top statement works fine. It's just that WHEREMET criteria I can't get to return the record set.



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Olaf,
BTW, this code:

SELECT *;
		FROM COMPANY;
		INNER JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS

Does work, but returns a lot more fields than I need/care about. So that's why I was just selecting down the ones I want to appear in my GRID.
So while "prettier" it brings back more data than I want. I'm just using this cursor to drive record selection in this table, which then drives other grids to be built for child tables when a record is clicked on from the grid.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

By the way, not that it probably matters, but that code (The IF clause) above is in the grid's INIT event.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

I'm not trying make the correct query, I try to find out what is killing your result, what's making it empty. Details about what fields to select just are totally unimportant at this stage, the fields you finally want can be put back later. I just want you to be my seeing eyes, as I don't have hands on your computer. Please continue, and we're getting forward.

Bye, Olaf.

 

[Olaf Doschke]

I don't know what you're talking about here, but if the IF clause is in the INIT event, the whole code is there, or is there some magic nowadays in which code like you show can be seen as one statement and still be scattered to be partly in Init and partly elsewhere.

Form.Init means that THIS=THISFORM. a form has no parent. This must error then.

Bye, Olaf.

 

[Scott24x7]

Olaf,
Ok, I think I just confused the issue. The INIT I mention is the GRID's INIT event, not the form. I put that statement, I just wanted to show how the whole thing is working, if that made it easier to understand or not.

So I'll just focus to the one that isn't working (the ELSE clause in the last code I posted above).
I'm beginning to think SQL won't work here... is it too complex for it?

Or maybe I'm not saying it clearly. As it stands, it doesn't work. WHERE clause in or out returns the same result. I added the field in the select statement CONTACT.CONTACTWHEREMET so that SELECT would try to get some data from the CONTACT table.
Maybe to simplify for now, I take out the CTSTATUS and see if I can make it work.
It just feels like "magic" to me when I'm trying to "reverse" a relationship from child to parent, where the parent is the table that the data I need is coming from, only when it matches to a condition in the child.



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Ok, I found a mistake (sort of) actually more some other things that were processing after the pick of the INTRODUCTION.
I'm making some change now. It wasn't executing the SELECT at all, but instead filtering on a condition that could never be met, so the grid was showing blank.
It might be working.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

The whole thing has become too messy.
I've taken a different approach that doesn't need the joins, and I'm back to a filter.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[JRB-Bldr]

Yes but I came seeking filter condition.

I'm back to a filter.

Filters are seldom a good approach.
Yes, if the filter is very simple then it might work, but otherwise I'd recommend that you avoid them.

Olaf's suggested approach is MUCH better. I'd recommend taking the time to understand what he is suggesting and go with that route.

Can you please answer open questions?

Perhaps answering Olaf's questions as asked, will enable all of us to assist you better.

Good Luck,
JRB-Bldr

 

[Scott24x7]

The approach didn't work it's not worth spending more time on now, I've already moved on. Thanks for your help.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."