×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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.

Students Click Here

Left Inner Join

Left Inner Join

Left Inner Join

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

RE: Left Inner Join

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

RE: Left Inner Join

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

RE: Left Inner Join

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

RE: Left Inner Join

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

RE: Left Inner Join

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

RE: Left Inner Join

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

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! Already a Member? Login


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