Solved

Interbase 5.5 Left Join (easy question lots of points)

Posted on 2004-03-25
14
1,323 Views
Last Modified: 2013-12-09
Ok I know this is a simple question, but after searching 4 or 5 interbase pdf files and google....

Can anyone please tell me how to do a LEFT JOIN in Interbase 5.5?


I am also using the old style SQL joins

select p.parentName, c.ChildName from Parent p, Child c where p.parent = c.parent;


I need to know the syntax to be used in the where clause?

I assigned so many point because I would like an answer within the next 30 minutes or so.

Thanks,
matchbx
0
Comment
Question by:matchbx27
  • 7
  • 7
14 Comments
 
LVL 3

Expert Comment

by:idt
ID: 10680636
Select L.fieldOne, R.FieldOne from
LeftTable L left outer join RightTable R
on (L.fieldname=R.fieldname)
where L.field=someval
order by ...

Actually there is no left join, its a left outer join.  All the rows from the left table and column from the right where the hoin criteria matches.

Daniel P.
0
 

Author Comment

by:matchbx27
ID: 10680695
I don't mean to be so hard IDT, however, you did not read my post.

I am using the old style join...
I gave a simplistic example...

my join is in the where clause...

WHERE p.Parent = c.Parent

I have tried the following that works on other databases

p.parent *= c.Parent
p.parent (+) = c.parent
p.parent *>= c.parent

What works with interbase 5.5.
0
 
LVL 3

Expert Comment

by:idt
ID: 10680714
There is no left outer join in SQL89 syntax, some databases supported it through wacky join syntax, like oracle had a += or something like that.
So I beleive that you must use SQL92 syntax.


Further, you can put additional predicates in the on clause:
on (L.fieldname=R.fieldname and R.fieldX<>1 and ...)
or put the additonal predicates in the where clause.

Do check the query plan, to ensure that additional predicate placements do not cause the query to exclude indices where they should be used.

Daniel P.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 3

Expert Comment

by:idt
ID: 10680749
Why must you use the SQL89 syntax?
I may be able to help you come up with another solution
0
 

Author Comment

by:matchbx27
ID: 10681568
Since I am new to Interbase 5.5 (this is a project for another department), I was under the assumption that Interbase 5.5 only supported SQL 89.  It seems as though I can use SQL 92.


Here's my problem:  I have to do a 7 table join.  6 of the table is 1 to 1. 1 table is a one to many.  In case you didn't notice, I'm doing this to create a view.

SQL 89 Version
Create View nvTotalPartsProduced1 (ProdLogID, ProdDate, ProdShift, JobNumber, PartNumber, TotalPartsProduced, ScrapCode, ScrapQty) as


SELECT
      PLH.PRODLOG_ID,
      PLH.PRODDATE,
      PSH.PRODSHIFT,
      JH.JOB_NUMBER,
      PI.PART_NUMBER,
      PPH.QUANTITY,
      PSCH.QUANTITY,
      SI.NAME
FROM
      PARTINFO PI,
      PRODLOGHIST PLH,
      PRODSHIFTHIST PSH,
      JOBHIST JH,
      PRODPARTHIST PPH,
      PRODSCRAPHIST PSCH,
      SCRAPINFO SI
WHERE
      PLH.PRODSHIFT_ID = PSH.PRODSHIFT_ID AND
      PLH.PRODLOG_ID = PPH.PRODLOG_ID AND
      PLH.JOB_ID = JH.JOB_ID AND
      PI.PART_ID = PPH.PART_ID AND
      PLH.PRODLOG_ID = PSCH.PRODLOG_ID AND        (This is where I need a left outer join)
      PSCH.SCRAP_ID = SI.SCRAP_ID;


SLQ 92 Version

Create View nvTotalPartsProduced1 (ProdLogID, ProdDate, ProdShift, JobNumber, PartNumber, TotalPartsProduced, ScrapCode, ScrapQty) as


SELECT
      PLH.PRODLOG_ID,
      PLH.PRODDATE,
      PSH.PRODSHIFT,
      JH.JOB_NUMBER,
      PI.PART_NUMBER,
      PPH.QUANTITY,
      PSCH.QUANTITY,
      SI.NAME
FROM
      ((((((PRODLOGHIST PLH JOIN PRODSHIFTHIST PSH ON PLH.PRODSHIFT_ID = PSH.PRODSHIFT_ID)
      JOIN JOBHIST JH ON PLH.JOB_ID = JH.JOB_ID)
      JOIN PRODPARTHIST PPH ON PLH.PRODLOG_ID = PPH.PRODLOG_ID)
      JOIN PARTINFO PI ON PI.PART_ID = PPH.PART_ID)
      LEFT OUTER JOIN PRODSCRAPHIST PSCH ON PLH.PRODLOG_ID = PSCH.PRODLOG_ID)  
      JOIN SCRAPINFO SI ON PSCH.SCRAP_ID = SI.SCRAP_ID);


The left outer join in the SQL 92 Version appears to work the same as a normal join.
0
 
LVL 3

Expert Comment

by:idt
ID: 10681975
Your syntax appears correct, except it looks like you have the last two columns reversed.

When you say that it works the same as a normal (inner) join, do you mean you are stiff having problems and the query has the wrong result set,

or are you commenting that the syntax is the same for join and left outer join ?

0
 

Author Comment

by:matchbx27
ID: 10681992
the result set that is returned is the same.
0
 

Author Comment

by:matchbx27
ID: 10682000
Thanks for the info on the last two columns.
0
 
LVL 3

Expert Comment

by:idt
ID: 10682093
Need clarification:

1 )for PRODSCRAPHIST.PRODLOG_ID, is there;
   A. always AT LEAST one row matching  PRODLOGHIST.PRODLOG_ID;
   B. sometimes one row  matching  PRODLOGHIST.PRODLOG_ID;
   C. always one row matching  PRODLOGHIST.PRODLOG_ID;

2) Do you get different behavior executing the query / selecting from the view


Although even without further clarification,

When I look at your tables, SCRAPINFO should be a left outer join aswell.

0
 
LVL 3

Accepted Solution

by:
idt earned 250 total points
ID: 10682183
Since SCRAPINFO is joined on  PRODSCRAPHIST, if there was no  PRODSCRAPHIST row, you would not get the entire joined row.  Effectively making the query a straight inner join.
0
 

Author Comment

by:matchbx27
ID: 10686547
1 )for PRODSCRAPHIST.PRODLOG_ID, is there;
   C. always one row matching  PRODLOGHIST.PRODLOG_ID;


However looking at it a little differently

for PRODLOGHIST.PRODLOG_ID there is
      0, 1 or many rows matching PRODSCRAPHIST.PRODLOG_ID


Thanks for claifying how SCRAPINFO should be joined.  That may be my problem.  I'm going to try that now.

0
 

Author Comment

by:matchbx27
ID: 10686632
Changing the last join to a left join did the trick...  new query is as follows:


Create View nvTotalPartsProduced1 (ProdLogID, ProdDate, ProdShift, JobNumber, PartNumber, TotalPartsProduced, ScrapCode, ScrapQty) as


SELECT
     PLH.PRODLOG_ID,
     PLH.PRODDATE,
     PSH.PRODSHIFT,
     JH.JOB_NUMBER,
     PI.PART_NUMBER,
     PPH.QUANTITY,
     PSCH.QUANTITY,
     SI.NAME
FROM
     ((((((PRODLOGHIST PLH JOIN PRODSHIFTHIST PSH ON PLH.PRODSHIFT_ID = PSH.PRODSHIFT_ID)
     JOIN JOBHIST JH ON PLH.JOB_ID = JH.JOB_ID)
     JOIN PRODPARTHIST PPH ON PLH.PRODLOG_ID = PPH.PRODLOG_ID)
     JOIN PARTINFO PI ON PI.PART_ID = PPH.PART_ID)
     LEFT OUTER JOIN PRODSCRAPHIST PSCH ON PLH.PRODLOG_ID = PSCH.PRODLOG_ID)  
     LEFT JOIN SCRAPINFO SI ON PSCH.SCRAP_ID = SI.SCRAP_ID);


Thanks for the help idt

matchbx
0
 
LVL 3

Expert Comment

by:idt
ID: 10687235
Glad it all worked out.  Sorry we didn't meet your 30 minute window.. next time :)
0
 

Author Comment

by:matchbx27
ID: 10687246
That's ok... I knew I was dreaming anyway.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server memory sizing - reallocation 16 88
T-SQL: New to using transactions 9 54
corrupt Databases 9 80
Star schema daily updates 2 35
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question