Join between two tables 1

[shawnwmorris]

I am new with FoxPro 9.0 (I know it is antiquated but it is what we have here at my new job)

I am attempting to create a query that will show all of the records from one table (Types) and the count of the occurrences on from another table. It sounds to me like a simple Left OUTER Join but the problem is that only the matching records from both tables are returned. I'd like to show all of the Types even if there isn't any instances of them in the occurrences table. My ideal results would be:

Type Count of occurances
Type 1 234
Type 2 59
Type 3 0 or NULL
Type 4 0 or NULL
Type 5 5
Type 6 89

My SQL is below:
SELECT PolicyIndType.poltype, COUNT(riskfile.poltype) FROM Policyindtype LEFT OUTER JOIN riskfile ON Policyindtype.Poltype = riskfile.poltype WHERE riskfile.cancelled >= DATE(YEAR(ldFirstofMonth)-1,01,01) AND riskfile.cancelled < DATE(YEAR(EndThisYear)-1,MONTH(EndThisYear),DAY(EndThisYear)) AND riskfile.company='002' GROUP BY PolicyIndType.Poltype

Where am I going wrong?

 

[Olaf Doschke]

You have conditions on the table you left joined, in detail about riskfile.cancelled

Whenever you have such conditions, you turn an outer join to an inner join, as for checking the condition there has to be data, conditions can't be checked on query rows having no match.

This is that way in all database SQL dialects I know, this is not at all VFP specific. So rather a sql lesson to learn, than a VFP lesson.

Put the conditions as HAVING or add ISNULL( riskfile.cancelled) OR (other conditions on riskfile)

Bye, Olaf.

 

[Olaf Doschke]

Actually the best way would be to make such conditions on riskfile join conditions. That keeps the nature of the join as outer join and will keep the count at 1 for COUNT(*) or 0 for COUNT(riskfile.poltype).
Besides that, better name columns of aggregates, or VFP generates names like expr1. Also not only VFP does so.

That said, don't be afraid you make a bad impression, I've seen other developers not getting it right, too. SQL is a beast and the diversity of dialects and db specific behaviour is not making it simpler, but if I think of VFP sql specific quirks, this is not one of them.

Bye, Olaf.

 

[JRB-Bldr]

This is that way in all database SQL dialects I know, this is not at all VFP specific. So rather a sql lesson to learn, than a VFP lesson.

Maybe a visit to the following will explain it for ALL SQL dialects.
[URL unfurl="true"]http://www.datamartist.com/sql-inner-join-left-outer-join-full-outer-join-examples-with-syntax-for-sql-server[/url]

Good Luck,
JRB-Bldr

 

[shawnwmorris]

Thanks for the help.

I am having trouble understanding though how to use the HAVING statement in FoxPro. I am attempting to use HAVING statement for just 1 condition first to get a handle on it but have been getting an error that says: "SQL: HAVING clause is invalid"

SELECT PolicyIndType.poltype, COUNT(riskfile.poltype) AS 'Count' FROM Policyindtype LEFT OUTER JOIN riskfile ON Policyindtype.Poltype = riskfile.poltype GROUP BY PolicyIndType.Poltype HAVING riskfile.company='002'

Obviously I've missed something here.

 

[Mike Lewis]

The point about HAVING is that it applies to the group, not the individual records. As far as I can see, that's not what you want.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[shawnwmorris]

Ah okay, so I need to filter the individual records in riskfile records before I count them?

 

[Olaf Doschke]

Well, you can use Having, but with the names of the aggregated fields, as said you should name them. Having is no exclusive solution, though, I also mentioned making where clauses join clauses. That's even the major and best solution. Keeping the outer join nature means filtering data of the joined table WHILE joining, not AFTER joining. So use join conditions and not WHERE conditions. It's even simpler, as you can keep the same conditions, just move them to the join.

By the way, but just as a side hint: You can use having on company, too, it would just need to be added to the query result and be a field you group by. Then you'D (theoretically) create data for all comanies and as aftermath (having) cut out only the data from company 002, but as the engine optimizes, what it does, that would not literally mean creating all extra data of other companies and then throwing them away, VFP would optimize that, too. So using HAVING as a solution, means more work.

@jrbbuilder: I already commented on these set theory related disgrams elsewhere. The are very easy to get a grip, but they do not really help understanding the point of why WHERE and JOIN clauses have different effects on the result. I partly hate these ilustration of join types for being incomplete.

Different dialects of SQL will not differ in respect of these simple joins of two tables, but there's much more to it and dialects may give different results on same SQL queries wuth different datrabases. At least I can rule out that's a root cause for this not working in VFP, it will not work anywhere, that was my main point.

Bye, Olaf.

 

[shawnwmorris]

@Olaf thanks so much for the input. I understand what you mean by filtering while joining rather than after. MY problem is syntax now. I have created the following which works to give me everything (no filtering anywhere)

SELECT Policyindtype.poltype, COUNT(riskfile.poltype) AS 'Count' FROM Policyindtype LEFT OUTER JOIN riskfile ON Policyindtype.poltype = riskfile.poltype GROUP BY PolicyindType.poltype

However when I attempt to filter in the join I get an error saying that I have an invalid Having statement.

I guess I'm not sure where in the query to put the having statements. I have tried moving to every position and still get an error.

 

[JRB-Bldr]

I'm not sure where in the query to put the having {or WHERE} statements

SELECT Policyindtype.poltype,;
[indent]COUNT(riskfile.poltype) AS Count;
FROM Policyindtype;
LEFT OUTER JOIN riskfile ON Policyindtype.poltype = riskfile.poltype;
WHERE [b]<any specific Policyindtype criteria, if any needed>[/b];
AND [b]<any specific riskfile criteria, if any needed>[/b];
GROUP BY PolicyindType.poltype;
INTO CURSOR aaa[/indent]

SELECT aaa
BROWSE

Try something like above.

Good Luck,
JRB-Bldr

 

[shawnwmorris]

@JRB-Bldr Thanks for the input. I don't need to filter anything from PolicyIndType, I have adapted the SQL but it still give only 7 of the 9 types. It is basically the same SQL as the first actually.

SELECT Policyindtype.poltype,;
 COUNT(riskfile.poltype) AS 'Type_Count';
 FROM Policyindtype LEFT OUTER JOIN riskfile ON Policyindtype.poltype = riskfile.poltype;
 WHERE (riskfile.cancelled >= DATE(YEAR(ldFirstofMonth)-1,01,01) AND riskfile.cancelled < DATE(YEAR(EndThisYear)-1,MONTH(EndThisYear),DAY(EndThisYear)) AND riskfile.company='002');
 GROUP BY PolicyindType.poltype

 

[shawnwmorris]

GOT IT!
Here is the final:

SELECT Policyindtype.poltype,;
 COUNT(riskfile.poltype) AS 'Type_Count';
 FROM Policyindtype LEFT OUTER JOIN riskfile ON Policyindtype.poltype = riskfile.poltype AND riskfile.cancelled >= DATE(YEAR(ldFirstofMonth)-1,01,01);
 AND riskfile.cancelled < DATE(YEAR(EndThisYear)-1,MONTH(EndThisYear),DAY(EndThisYear)); 
 AND riskfile.company='002';
 GROUP BY PolicyindType.poltype

Thanks to everyone for the help. It was definitely a brain bender for me and I couldn't have done it without everyone's input. Thanks again.

 

[Olaf Doschke]

Is it really so difficult to follow what I explained?

Concentrate on one thing first, and that's the JOINs, please, and put having aside, it's harder to apply anyway.

SELECT Policyindtype.poltype, COUNT(riskfile.poltype) AS 'Count' FROM Policyindtype LEFT OUTER JOIN riskfile ON Policyindtype.poltype = riskfile.poltype ;
[highlight #FCE94F]AND (riskfile.cancelled >= DATE(YEAR(ldFirstofMonth)-1,01,01) AND riskfile.cancelled < DATE(YEAR(EndThisYear)-1,MONTH(EndThisYear),DAY(EndThisYear)) AND riskfile.company='002');[/highlight]
GROUP BY PolicyindType.poltype

A join condition is not limited to specify a match by foreign and primary key. That's the most often use, but in case of an OUTER join you MUST put further conditions into the JOIN, or your OUTER join turns an INNER join, which YOU DO NOT WANT. It is possible, BUT YOU DON'T HAVE AN OUTER JOIN ANYMORE., when you do so. If you don't do so, you indirectly filter out all rows with no match in the riskfile table, too, though you want them in your OUTER join.

HAVING is just another way of applying conditions as aftermath of WHERE, but WHERE clauses sit at the least ideal position in that matter.

Let me correct what JRBBLDR gave:

SELECT Policyindtype.poltype,;
COUNT(riskfile.poltype) AS Count;
FROM Policyindtype;
LEFT OUTER JOIN riskfile ON Policyindtype.poltype = riskfile.poltype;
WHERE <any specific Policyindtype criteria, if any needed>;
[s]AND <any specific riskfile criteria, if any needed>;[/s]
GROUP BY PolicyindType.poltype

The moment you add any condition on riskfile (the joined table) you turn the OUTER to an INNER join, or put in code:

SELECT Policyindtype.poltype,;
COUNT(riskfile.poltype) AS Count;
FROM Policyindtype;
LEFT [highlight #FCE94F]INNER[/highlight] JOIN riskfile ON Policyindtype.poltype = riskfile.poltype;
WHERE <any specific Policyindtype criteria, if any needed>;
AND <any specific riskfile criteria, if any needed>;
GROUP BY PolicyindType.poltype

Technically you can always use whatever filter clauses you like in WHERE, but the nature of the join changes, when you do so. Not even though there is no riskfile data to apply conditions to, if there was no match but because there is no riskfile data to apply conditions to. If riskfile data is missing, the WHERE conditions are not met, so the whole record is dropped from the result. This way no Policyindtype with no riskfile match remains and thus you get no count for them, not even 0 or 1, no count at all.

Bye, Olaf.

 

[Mike Lewis]

I guess I'm not sure where in the query to put the having statements. I have tried moving to every position and still get an error.

In general, VFP allows you to put clauses within a SELECT statement in any order (unlike many other dialects of SQL). The HAVING works just the same whether you put it before or after the GROUP BY, or even before the WHERE. The only documented constraint is that you can't put it after an INTO (which doesn't apply in your case).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, and the other important constraint is, it doesn't work on the fields of the source tables but on result set fields. To get a filter for company, for example, it would need to be in the field list, first.

This is something, which differs from eg T-SQL (MSSQL) HAVING clauses.

Bye, Olaf.