matchbx27
asked on
Interbase 5.5 Left Join (easy question lots of points)
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
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
ASKER
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.
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.
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.
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.
Why must you use the SQL89 syntax?
I may be able to help you come up with another solution
I may be able to help you come up with another solution
ASKER
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.
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.
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 ?
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 ?
ASKER
the result set that is returned is the same.
ASKER
Thanks for the info on the last two columns.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
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
Glad it all worked out. Sorry we didn't meet your 30 minute window.. next time :)
ASKER
That's ok... I knew I was dreaming anyway.
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.