left join in TIBDataSet

Thread View: borland.public.interbase.sql

5 messages

[T] Back to Threads [V] Tree View [G] Group Articles

5 total messages Started by Sergio Gonzalez Wed, 11 Dec 2002 18:51

#11217

Author: Sergio Gonzalez
Date: Wed, 11 Dec 2002 18:51

28 lines
1763 bytes

Hi there!

First of all. I don't have a "real" problem.
Actually, it works, but I don't know if this is the best way to do it.

I need to show in a dbgrid the fields of 2 tables.
One of them (COSTO) is the "detail" of another table (tough I think it
doesn't matter here)

I have in a SelectSQL of a TIBDataSet the next instruction

SELECT ID, ID_TCosto, Descripcion, Valor, Accion
    FROM COSTO C
    LEFT JOIN TCOSTO TC
    ON C.ID_TCOSTO = TC.ID_TCOSTO
    WHERE C.ID = :ID

1) I didn't specify in which table is each field. Should I?

2) what's the difference with:

SELECT C.ID, C.ID_TCosto, TC.Descripcion, C.Valor, TC.Accion
    FROM COSTO C,  TCOSTO TC
    WHERE C.ID = :ID and C.ID_TCOSTO = TC.ID_TCOSTO

Thank you very much!

Sergio

Re: left join in TIBDataSet

#11218

Author: Harun Bolat
Date: Wed, 11 Dec 2002 19:18

27 lines
1907 bytes


> SELECT ID, ID_TCosto, Descripcion, Valor, Accion
>     FROM COSTO C
>     LEFT JOIN TCOSTO TC
>     ON C.ID_TCOSTO = TC.ID_TCOSTO
>     WHERE C.ID = :ID
> 1) I didn't specify in which table is each field. Should I?

yes but all field that you select have to belong only one table. for example
if ID_TCOSTA field found COSTA and TCOSTO tables , select statement raise
error.

> 2) what's the difference with:

> SELECT C.ID, C.ID_TCosto, TC.Descripcion, C.Valor, TC.Accion
>     FROM COSTO C,  TCOSTO TC
>     WHERE C.ID = :ID and C.ID_TCOSTO = TC.ID_TCOSTO

 SELECT ID, ID_TCosto, Descripcion, Valor, Accion
     FROM COSTO C
     JOIN TCOSTO TC    ON C.ID_TCOSTO = TC.ID_TCOSTO
     WHERE C.ID = :ID

 if drop left, results are same but first sql (join SQL) execution time less
than second.
but with left clause first sql result set maybe different.

Re: left join in TIBDataSet

#11219

Author: Jeff Overcash (TeamB)
Date: Wed, 11 Dec 2002 19:56

26 lines
1960 bytes


Harun Bolat wrote:
> 
> > SELECT ID, ID_TCosto, Descripcion, Valor, Accion
> >     FROM COSTO C
> >     LEFT JOIN TCOSTO TC
> >     ON C.ID_TCOSTO = TC.ID_TCOSTO
> >     WHERE C.ID = :ID
> > 1) I didn't specify in which table is each field. Should I?
> 
> yes but all field that you select have to belong only one table. for example
> if ID_TCOSTA field found COSTA and TCOSTO tables , select statement raise
> error.
> 

No it does not raise an error.  IB does allow ambiguous SQL statements.

-- 
Jeff Overcash (TeamB) 
      (Please do not email me directly unless  asked. Thank You) 
Maybe it was infatuation or the thrill of the chase.
Maybe you were always beyond my reach and my heart was playing safe.
But was that love in your eye I saw or the reflection of mine?
Give me time, won't you give me that time!
Welcome back to the circus.            (Fish)

Re: left join in TIBDataSet

#11220

Author: Brian Cook
Date: Wed, 11 Dec 2002 20:30

7 lines
1628 bytes

> No it does not raise an error.  IB does allow ambiguous SQL statements.

That is weird.  It appears to use the fields from the "joined to" table
(TCOSTO in Sergio's case).  Is InterBase supposed to behave this way?  I
wouldn't think this behaviour is very SQL compliant.

- Brian

Re: left join in TIBDataSet

#11222

Author: Wayne Niddery [TeamB]
Date: Thu, 12 Dec 2002 05:00

18 lines
1943 bytes

"Brian Cook" <bcook@rowdydogsoftware[REMOVE].com> wrote in message
news:3df7a06e$1@newsgroups.borland.com...
> > No it does not raise an error.  IB does allow ambiguous SQL statements.
>
> That is weird.  It appears to use the fields from the "joined to" table
> (TCOSTO in Sergio's case).  Is InterBase supposed to behave this way?  I
> wouldn't think this behaviour is very SQL compliant.

I wish it would raise an error but it does not. So it's good advice to
always qualify field names with the table or table alias as an ambiguous
reference can result in incorrect results.


--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
It used to be that other people's achievements were considered an
inspiration, not a grievance.

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads