INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Join between two tables

Join between two tables

(OP)
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?

RE: Join between two tables

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.

RE: Join between two tables

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.

RE: Join between two tables

(OP)
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.

RE: Join between two tables

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

RE: Join between two tables

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

RE: Join between two tables

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.

RE: Join between two tables

(OP)
@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.

RE: Join between two tables

Quote (Shawn)

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

CODE -->

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>; AND <any specific riskfile criteria, if any needed>; GROUP BY PolicyindType.poltype; INTO CURSOR aaa
SELECT aaa BROWSE

Try something like above.

Good Luck,
JRB-Bldr


RE: Join between two tables

(OP)
@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.

CODE --> SQL

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 

RE: Join between two tables

(OP)
GOT IT!
Here is the final:

CODE -->

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.

RE: Join between two tables

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.

CODE

SELECT Policyindtype.poltype, COUNT(riskfile.poltype) AS '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 

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:

CODE

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>;
AND <any specific riskfile criteria, if any needed>;
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:

CODE

SELECT Policyindtype.poltype,;
COUNT(riskfile.poltype) AS Count;
FROM Policyindtype;
LEFT INNER 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.

RE: Join between two tables

Quote:

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

RE: Join between two tables

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close