Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 8 of 8 total |
Select from three tables |
Fri, Feb 6 2009 1:38 PM | Permanent Link |
Joel Schaubert | Can I join from three tables in a DBIASMQuery as shown here?
DBISAM Engine 3.X http://www.firstsql.com/tutor3.htm This query performs a 3 table join: SELECT name, qty, descr, color FROM s, sp, p WHERE s.sno = sp.sno AND sp.pno = p.pno I'm having trouble getting dbisam to accept the syntax, it seems surprised by the WHERE clause. I did find another way to do it by using DBISAM Query for the first 2 tables, add the third table and then create NEW lookup fields that join over to the third table. But I'd greatly prefer to do it using just plain SQL query in a single DBISAMQuery object if possible. Thanks, Joel |
Fri, Feb 6 2009 2:27 PM | Permanent Link |
"Jeff Cook" | Joel Schaubert wrote:
> Can I join from three tables in a DBIASMQuery as shown here? > > DBISAM Engine 3.X > > http://www.firstsql.com/tutor3.htm > > This query performs a 3 table join: > SELECT name, qty, descr, color > FROM s, sp, p > WHERE s.sno = sp.sno > AND sp.pno = p.pno > > I'm having trouble getting dbisam to accept the syntax, it seems > surprised by the WHERE clause. > > I did find another way to do it by using DBISAM Query for the first 2 > tables, add the third table and then create NEW lookup fields that > join over to the third table. But I'd greatly prefer to do it using > just plain SQL query in a single DBISAMQuery object if possible. > > Thanks, > Joel Kia Orana Joel I'm on v3.30 and I can't see anything wrong with your SQL either. However I wouldn't have coded it that way myself - this is how I would have done it:- SELECT name, qty, descr, color FROM s JOIN sp ON (sp.sno = s.sno) JOIN p ON (p.pno = sp.pno) .... and presumably DBISAM will be more specific in it's error message if the syntax is wrong as the various bits are on separate lines. Kia Manuia Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz + Joan and Jeff Cook The Cooks Oasis www.cookislandsoasis.com |
Fri, Feb 6 2009 6:19 PM | Permanent Link |
"Robert" | "Jeff Cook" <jeffc@aspect.co.nz> wrote in message news:4F773830-A6DA-4E6B-864A-F697723B88D2@news.elevatesoft.com... > Joel Schaubert wrote: > > Kia Orana Joel > > I'm on v3.30 and I can't see anything wrong with your SQL either. > are you sure the problem is not with the AND? maybe it should be where (x=y) and (a=b) so that it does not try to resolve the AND as a function x = y and b Robert |
Sat, Feb 7 2009 2:46 AM | Permanent Link |
Joel Schaubert | "Jeff Cook" wrote:
Joel Schaubert wrote: > Can I join from three tables in a DBIASMQuery as shown here? > > DBISAM Engine 3.X > > http://www.firstsql.com/tutor3.htm > > This query performs a 3 table join: > SELECT name, qty, descr, color > FROM s, sp, p > WHERE s.sno = sp.sno > AND sp.pno = p.pno > > I'm having trouble getting dbisam to accept the syntax, it seems > surprised by the WHERE clause. > > I did find another way to do it by using DBISAM Query for the first 2 > tables, add the third table and then create NEW lookup fields that > join over to the third table. But I'd greatly prefer to do it using > just plain SQL query in a single DBISAMQuery object if possible. > > Thanks, > Joel Kia Orana Joel I'm on v3.30 and I can't see anything wrong with your SQL either. However I wouldn't have coded it that way myself - this is how I would have done it:- SELECT name, qty, descr, color FROM s JOIN sp ON (sp.sno = s.sno) JOIN p ON (p.pno = sp.pno) Excellent --- Using the 2 JOIN's instead of WHERE's and AND's worked perfectly. Thanks ! Joel |
Sat, Feb 7 2009 3:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Joel
Can't quite remember just what syntax V3 used but something like SELECT name, qty, descr, color FROM s JOIN sp ON s.sno = sp.sno JOIN sp ON sp.pno = p.pno Might be correct Roy Lambert [Team Elevate] |
Sun, Feb 8 2009 8:58 PM | Permanent Link |
"Rita" | "Joel Schaubert" <foxyjoel@gmail.com> wrote in message news:7EEBBE6E-7D2D-40E1-9DE3-> > This query performs a 3 table join: > SELECT name, qty, descr, color > FROM s, sp, p > WHERE s.sno = sp.sno > AND sp.pno = p.pno > Try this dont forget the brackets before AND + after SELECT A.sno, B.pno, B.sno, C.pno FROM TABLE1 A, TABLE2 B, TABLE3 C WHERE (A.sno = B.sno) AND (B.pno = C.pno) If it works its a miracle. Rita |
Tue, Feb 10 2009 7:58 AM | Permanent Link |
Joel Schaubert | Thanks to all who replied.
I've tried each of the suggestions and I've confirmed all of these syntaxes AND my original syntax do indeed work. I had a stray WHERE 1=1 packed into one of the SQL->Strings[N] objects that was giving me double WHERE's and made the original query go bad. Joel |
Tue, Feb 10 2009 10:20 AM | Permanent Link |
"Rita" | "Joel Schaubert" <foxyjoel@gmail.com> wrote in message news:CD57F01B-4385-481F-BD4E-1E390CF66DA4@news.elevatesoft.com... > I've tried each of the suggestions and I've confirmed all of these > syntaxes AND my > original syntax do indeed work. > Wow SQL is so flexible such a variety of different methods. > I had a stray WHERE 1=1 packed into one of the SQL->Strings[N] > objects that was > giving me double WHERE's and made the original query go bad. > Great I had visions of u losing data from my input, but it worked my end honest Now get back to work. Rita |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |