Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 17 total |
Left outer joins not working |
Thu, Oct 15 2009 7:40 AM | Permanent Link |
Malcolm | Hi All,
I have been trying to use the left outer join but without success and I can't see what I am doing wrong, your help would be appreciated. I have been "playing" with 2 tables (trying to do something simple to start with) wanting to list all records from one with additional data added for some from the second. One table contains execs and shop numbers, the other shop numbers, ECR types and phone Numbers. I am expecting a result containing 55 rows 28 of which hold the additional data, but as you can see I only receive the 28 rows. The tables contain identical information regarding shop numbers i.e. 1 to 55. The plan result is:- ================================================================================ SQL statement (Executed with 4.26 Build 3) ================================================================================ select d1.exec, d.Name, d.ECRType, d.POSTelephone from JJWLocation d1 left outer join Location d on d.locationno = d1.locationno where d.ECRType =3 Tables Involved --------------- JJWLocation (d1) table opened shared, has 55 rows Location (d) table opened shared, has 55 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows WHERE Clause Execution ---------------------- Join Ordering ------------- The driver table is the JJWLocation table (d1) The JJWLocation table (d1) is joined to the Location table (d) with the LEFT OUTER JOIN expression: d1.locationno = d.locationno Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further The expression: d.ECRType = 3 will be applied to each candidate row in the result set as the result set is generated due to the Location table (d) being the target of an OUTER join Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: d1.locationno = d.locationno is OPTIMIZED ================================================================================ >>>>> 28 rows affected in 0.031 seconds ================================================================================ |
Thu, Oct 15 2009 9:03 AM | Permanent Link |
"Robert" | "Malcolm" <Malcolm@jjwilsonltd.com> wrote in message news:9FA99AD3-043F-4957-A553-BA4F91AF8805@news.elevatesoft.com... > > select d1.exec, d.Name, d.ECRType, d.POSTelephone > from JJWLocation d1 left outer join Location d on d.locationno = > d1.locationno > where d.ECRType =3 > d.ECRType is null for the rows that have no match on table d1. Robert |
Thu, Oct 15 2009 9:38 AM | Permanent Link |
Malcolm | Hi Robert,
Not quite with you. In table 2 the ECRType is either 1 or 12 . What I want to achive is a list of all locations (1 to 55) with each location showing the exec and name and for those locations that have an ECRType of 3 to show the ECRType and the telephone number. example Exec Name EcrType Phone AAA FRED 3 1234567890 BBB BERT NULL NULL CCC JO NULL NULL DDD JOE 3 0987654321 Regards Malcolm |
Thu, Oct 15 2009 9:52 AM | Permanent Link |
"Robert" | "Malcolm" <Malcolm@jjwilsonltd.com> wrote in message news:B4E0B297-0F5D-465C-9ABB-6737AA5860FD@news.elevatesoft.com... > Hi Robert, > > Not quite with you. > In table 2 the ECRType is either 1 or 12 . > > What I want to achive is a list of all locations (1 to 55) with each > location showing the exec and name and for those locations that have an > ECRType of 3 to > show the ECRType and the telephone number. > > example > > Exec Name EcrType Phone > AAA FRED 3 1234567890 > BBB BERT NULL NULL > CCC JO NULL NULL > DDD JOE 3 0987654321 > Then you need to test for (EcrType = 3) or (EcrType = NULL) in the WHERE clause. Your WHERE clause negates the left join, since it will only select EcrType = 3 which by definition means they are rows that have a match on the two tables. Robert > Regards > Malcolm > |
Thu, Oct 15 2009 10:18 AM | Permanent Link |
"John Hay" | Malcolm
> What I want to achive is a list of all locations (1 to 55) with each location showing the exec and name and for those locations that have an ECRType of 3 to > show the ECRType and the telephone number. > > example > > Exec Name EcrType Phone > AAA FRED 3 1234567890 > BBB BERT NULL NULL > CCC JO NULL NULL > DDD JOE 3 0987654321 If you want to include all records but hide the ecrtype and phone for those records where the ecrtype is not 3 then something like the following should work select d1.exec, d.Name, if(d.ECRType = 3 then d.ecrtype else null),if(d.ecrtype=3 then d.POSTelephone else null) from JJWLocation d1 left outer join Location d on d.locationno = d1.locationno John |
Thu, Oct 15 2009 10:18 AM | Permanent Link |
Malcolm | Hi Robert
As far as I am aware, please correct me if i am wrong, the point of a left outer join was to include all records from the left table and the detail from the right table where the claus conditions are met. If you look at the Plan I do have a where ECRType =3, which is why in my example I show the ECRType and phone number for thos records that have an ECRType of 3 and null in those fields for those that do not. I expect the sql to output null values because the condition is not met. Regards Malcolm |
Thu, Oct 15 2009 10:21 AM | Permanent Link |
Malcolm | Hi Again
Sorry in my response with the example I should have said all records in the second table have an ECRType of either 3 or 12. Regards Malcolm |
Thu, Oct 15 2009 11:07 AM | Permanent Link |
"Robert" | "Malcolm" <Malcolm@jjwilsonltd.com> wrote in message news:A3A512C7-274A-47A1-BD5B-0EB589448E0B@news.elevatesoft.com... > Hi Robert > As far as I am aware, please correct me if i am wrong, the point of a left > outer join was to include all records from the left table and the detail > from the > right table where the claus conditions are met. > Wrong. The where clause filter will be applied to all selected rows, after the join is applied. Robert > If you look at the Plan I do have a where ECRType =3, which is why in my > example I show the ECRType and phone number for thos records that have an > ECRType of 3 and null in those fields for those that do not. I expect the > sql to output null values because the condition is not met. > > Regards > Malcolm > |
Thu, Oct 15 2009 1:51 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Malcolm,
<< As far as I am aware, please correct me if i am wrong, the point of a left outer join was to include all records from the left table and the detail from the right table where the claus conditions are met. >> SQL is 100% left-to-right, top-to-bottom, which means that the execution order is thus for filtering/selection: JOIN(s) WHERE HAVING TOP In your case, the LOJs are generating NULL values for the ECRType field in the result set, and then the WHERE clause is promptly removing the records with these ECRType NULL values. Robert's revised WHERE clause will do what you want. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Oct 16 2009 4:51 AM | Permanent Link |
Malcolm | Hi Tim / Robert
Thats someting else I've learnt about SQL. Many thanks for your help, I will go away and "play" some more Regards Malcolm |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |