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]

Why Filter? Do this as SQL query.

Bye, Olaf.

 

[Scott24x7]

Olaf,
I had considered that, but I have a number of other filter conditions which are based on criteria within the Company table (though only one criteria applied at a time). But I see your idea there, it might work for me because I'm using a grid to control the display of that table, so it might work.
I'm not strong with SQL and JOIN commands though. But thinking about it, maybe it is the better way to go.
Let me try to write a SQL statement for it based on advise you gave me about a year ago. If that doesn't work I'll come back with some code.
Thanks,
-S


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Scott24x7]

Hi Olaf,
Ok, I'm failing on this. Hope you can steer me in the right direction on this. The Grid this populates is a little trick because it already relies on a LEFT OUTER join to populate the "STATUS" value, and in this case the "Filter" condition has to match from a child table, so I'm not sure how to express that as SQL. The current statement looks like:

	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

So now I need to select based on the CONTACT table matching the "CONTACT.INTRODUCED" value matching the combobox value cboFilterIntroduction.VALUE matching the CONTACT.INTRODUCTION, where (is that a clue?) CONTACT.COMPANYID = COMPANY.COMPANYID

And load the result into that same CURSOR [COMPANYGRID] in this case.
Am I making any sense?
Thanks.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Olaf Doschke]

Now you need to add the filter condition, that's a WHERE in sql.

Bye, Olaf.

 

[Olaf Doschke]

Besides you want an INNER join here, you don't want ALL companies, only those for which (at least) one contact has a certain status.
Besides that you contradicted your own specification in that the contcts are joined by companyID. That's your join condition, certain status is your where condition.

Bye, Olaf.

 

[Scott24x7]

I may not be expressing it well, so bear with me.

So in this case would it be more like:

SELECT COMPANY.COMPANYID, COMPANY.COMPANYNAME, COMPANY.COMPANYCOUNTRY, COMPANY.COMPOANYINDUSTRYTYPE,;
[indent]CTSTATUS.COMPANYSTATUSDESCRIP, COMPANY.COMPANYDCRELATED, COMPANY.COMPANYCLIENTSTATUS, .F. AS LEDGERCONTROL;[/indent]
[indent]FROM COMPANY WHERE[/indent]
[indent]COMPANY.COMPANYID = CLIENT.COMPANYID; [/indent]
[indent]INNER JOIN ALLTRIM(CLIENT.INTRODUCTION) = ALLTRIM(THIS.PARENT.cboFilterIntroduction.VALUE) [/indent]
[indent]LEFT JOIN CTSTATUS ON STSTATUS.COMPANYSTUTUSTYPE = COMPANY.COMPANYCLIENTSTATUS[/indent]
[indent]INTO CURSOR COMPANYGRID READWRITE ORDER BY COMPANYNAME[/indent]

That is what I tried, but it did not work.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Olaf Doschke]

COMPANY.COMPANYID = CLIENT.COMPANYID is your join condition, not your WHERE clause


INNER JOIN ALLTRIM(CLIENT.INTRODUCTION) = ALLTRIM(THIS.PARENT.cboFilterIntroduction.VALUE) Is an incomplete JOIN, it doesn't specify what to join on that condition. Again, this rather looks like a WHERE condition.

RELATION: JOIN
FILTER: WHERE

It's mainly just a translation of terms.

Bye, Olaf.

 

[Scott24x7]

So the left join on CTSTATUS, does that come last? And I can put in more than one join?

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Scott24x7]

Trying to follow your comments, but this gives me a syntax error:

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

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Olaf Doschke]

You better understnd relations of your tables, but Companies are related to Clients and Clients are related to Clientstsatuses, correct?

Bye, Olaf.

 

[Scott24x7]

Sorry, I know this one gets a bit complicated.
There are 3 tables:
COMPANY
CONTACT
CTSTATUS

The company has a value 1 - 8 for "Status" but the code table has the values in plane language that the status equates to. So to get the wording from CTSTATUS to show in the status column in the grid, I do the LEFT JOIN to get that value in the array.

COMPANY is parent, CONTACT is child. CONTACT and COMPANY common key is COMPANYID.

Now there is a field in CONTACT called "WHEREMET" (or Introduction, in plane english) that says where this contact was met, like "WORLD EXPO". What I'm trying to accomplish is to show companies that have a contact matching the "WHEREMET" value, which is picked from a dropdown which gets its population with the following clause:
SELECT DISTINCT WHEREMET FROM CONTACT INTO ARRAY (This.SourceArray)

So that dropdown shows all the values for where met.

So I'm trying to bring all that together into a single display cursor, which is the COMPANYGRID CURSOR, which I then use to drive the rest of the tables in view. Clicking any company in that grid will automatically show the contacts for that company, which I'm not trying to filter, only the companies.

I hadn't considered this possibility when I first built the application, but as it's gotten more data, I find I would like to identify companies from the contacts, and where we were introduced to them.
Thanks.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Olaf Doschke]

Other wordings, you still have two joins one after the other, parent->child->grandchild COMPANY->CONTACT->CTSTATUS, two joins. Please lookup what a join is composed of: JOIN tablename ON condition.

You're missing the mere tablenames in your joins. In comparison to a RELATION: SET RELATION TO field INTO table, where field is just shorthand for the condition table1.field = table2.field

It's the same, just less condensed, therefore even simpler to undserstand than a relation. SQL is verbose on what to join under which condition. I see you bite your tongue while trying to formulate a query. Sorry, but I jsut won't give you this fish, I'll be very patient with you here, but you have to learn this.

Bye, Olaf.

 

[Scott24x7]

Yes but I came seeking filter condition, and I'm trying a path you suggest, but I don't get it... so it's not really helpful.
Thanks.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Olaf Doschke]

I'm here to help comprehend and understand, not to do work for free. You're refusing to learn. You even don't apply things I said, so of course this won't work out for you, if you refuse to take the advices.

Bye, Olaf.

 

[Olaf Doschke]

Take it one thing at a time:

You do:

INNER JOIN COMPANY.COMPANYID = CLIENT.COMPANYID

You applied my advice to use INNER here, but then you do LEFT JOIN CTSTATUS.

That last hitng aside, I said the general join syntax is JOIN tablename ON condition. That means:

INNER JOIN CLIENT ON COMPANY.COMPANYID = CLIENT.COMPANYID

CLIENT is the table to join COMPANY.COMPANYID = CLIENT.COMPANYID is the condition of the join.

Bye, Olaf.

 

[Scott24x7]

Well, it's closer but I can see I'm still missing the criteria. The "ON" clause COMPANY.COMPANYID = CONTACT.COMPANYID
Where do we get the CONTACT condition of CONTACT.WHEREMET = ...cboFilterIntroduction.Value ?

Right now the code runs but returns nothing in the CURSOR

	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;
		INTO CURSOR COMPANYGRID READWRITE ORDER BY COMPANYNAME

Am I right in the concept that the LEFT JOIN just replaces values in the CURSOR after the cursor is populated, based on the value in CTSTATUS? (In my head it seems like that's what actually happens).


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Olaf Doschke]

Joins are just joining data, they are not overlapping, replacing or overwriting anything. What columns/fields you pick into the result cursor is fully controlled by the field list before FROM, You join a sheet of paper to another, you get access to all fields of the joined table you add to the field you can pick from in the select field list. Just like the relation the join causes a positionig on both sides, also like a relation with a direction from left to right, master to slave. It makes an alignment of rows. Join type specifies whether not meeting the condition keeps the whole row (inner) out of the result, or just adds one side of it (left/right outer) and adds NULLs in the side missing a matching row. And source tables are not altered at all.

You do two inner joins here, because of what I already said: you don't want ALL companies, only those for which (at least) one contact has a certain status.
In the same manner you only want that companies having a matching CTSTATUS.COMPANYSTATUSTYPE, the CTSTATUS data will be filtered in the WHERE anyway.
You put a WHERE clause after the two joins. Conditions in there are all the mere data filter conditions, whoch don't have to do with joining/aligning rows.

Bye, Olaf.

 

[Scott24x7]

That didn't work either.
Still just a blank set.

	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

Still returns nothing.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

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

 

[Olaf Doschke]

The query looks OK now, despite of still using LEFT instead of INNER.

How about starting with a value you're sure would get a non empty result and put it in litereally instead of ALLTRIM(This.Parent.cboFilterIntroduction.VALUE) to put aside problems you might have with scope and the contro. Do you bind a grid to COMPANYGRID before or after doing that query? You obviuosly must do so afterwards.

SELECT *;
		FROM COMPANY;
		INNER JOIN CONTACT ON COMPANY.COMPANYID = CONTACT.COMPANYID;
		INNER JOIN CTSTATUS ON CTSTATUS.COMPANYSTATUSTYPE = COMPANY.COMPANYCLIENTSTATUS;
			WHERE ALLTRIM(CONTACT.CONTACTWHEREMET) = "WORLD EXPO"

Any error? For example about any non existing field names? Above you stated field name is WHEREMET instead of CONTACTWHEREMET.

Bye, Olaf.