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

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

"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

"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

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]

"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

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

"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