Left Inner Join

[JimReiley]

I have two tables I want to join. I want all the records in table a if they have a certain string in one field and all records in table b that match a field in a. I have tried more syntax's than I believed imaginable with absolutley no luck. It appears that pervasive doesn't support a left inner join or they call it something else. I do not want to use a select statement.

 

[mirtheil]

WHat syntax have you tried?
WHat results have you seen (errors, slow performance, etc)?
What versoin of Pervasive?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

Here's the query I used. The problem with it is it doesn't return to me ALL the records in the FreshJobs table and I need it to, evenif there are no matching records in InvHistoryDetail. The speed is excellent for as much as the query does.

FJMain.Query3.Close;
FJMain.Query3.SQL.Clear;
FJMain.Query3.SQL.Add('Select FreshJobs.*, InvHistoryDetail.*');
FJMain.Query3.SQL.Add('from FreshJobs');
FJMain.Query3.SQL.Add('Inner Join InvHistoryDetail on concat(FreshJobs.AdNumber,''05'')=InvHistoryDetail.MillerAdNumber');
FJMain.Query3.SQL.Add('where FreshJobs.FJInvoiceDate=? and');
FJMain.Query3.SQL.Add(' InvHistoryDetail.AncillaryType=15');
FJMain.Query3.SQL.Add(' Order by FreshJobs.AdExec, FreshJobs.ClientNumber,FreshJobs.AdNumber');

FJMain.Query3.Params[0].asDate := BilledDate;

 

[mirtheil]

What happens if you hard code the value (rather than use a parameter) as a test?
Does the query return the correct results in the PCC?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

I initially used the PCC and had eveything hard coded. It didn't make any difference. The result set was the same. The total records in FreshJobs that should be returned is 128 or so and it returns 74.

 

[mirtheil]

Sounds like you're going to need to open a support ticket with Pervasive. It might be a bug in the engine.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[JimReiley]

I called them. Havn't heard to much yet.

In the docs there is a reference to a NULL JOIN, which, as defined, is exactly what I want to do. Unfortunately there is no defiinition for a null join. Do you know what they're talking about?