[V4RB 0000496]: SQL-Problem

[Ivan Smahin]

Hello Claudius,

Monday, February 28, 2005, 5:31:30 PM, you wrote:

CS> Hi Ruslan,

CS> Am 27. Feb 2005 um 11:58 Uhr schrieb Ruslan Zasukhin:

>> On 2/27/05 12:39 PM, "vbt at paradigma.ukrcom.kherson.ua"
>> <vbt at paradigma.ukrcom.kherson.ua> wrote:
>>
>>> Summary:                    SQL-Problem
>>> Description:
>>> Following Code has result 0 records
>>>
>>> SELECT KO.Konto_Typ, KZ.Konto_Typ, B.Kategorie_ID, B.Betrag FROM 
>>> Buchungen
>>> B, Konten KO, Konten KZ
>>> WHERE B.Herk_Konto=KO.RecID
>>> AND B.Ziel_Konto=KZ.RecID
>>> AND B.Deleted='0'
>>> AND B.BDatum<='27.02.2005'
>>>
>>> but it should have a lot
>>
>> Hi Claudius,
>>
>> I will recommend redo query using new SQL92 syntax of joins.
>> This make query more clean.
>>
>>
>> SELECT
>>     KO.Konto_Typ, KZ.Konto_Typ, B.Kategorie_ID, B.Betrag
>> FROM
>>     Buchungen B join Konten KO ON B.Herk_Konto=KO.RecID
>>                 join Konten KZ ON B.Ziel_Konto=KZ.RecID
>> WHERE
>>     B.Deleted='0' AND B.BDatum<='27.02.2005'

CS> faster too?
CS> I don't know SQL92 syntax. I am a sybase hacker with learning by seeing
CS> and rebuilding ;-))
CS> The syntax you show me is a little bit confusing me.

CS> How is it realized when I want to do following?

CS> SELECT KO.Konto_Typ, KZ.Konto_Typ, B.Kategorie_ID, B.Betrag
CS> FROM Buchungen B, Konten KO, Konten KZ
CS> WHERE B.Herk_Konto=KO.RecID
CS> AND B.Ziel_Konto=KZ.RecID
CS> AND KO.Konto_Type = 1
CS> AND B.Deleted='0'
CS> AND B.BDatum<='27.02.2005'

CS> is it then

CS> SELECT
CS>      KO.Konto_Typ, KZ.Konto_Typ, B.Kategorie_ID, B.Betrag
CS> FROM
CS>      Buchungen B join Konten KO ON B.Herk_Konto=KO.RecID
CS>                  join Konten KZ ON B.Ziel_Konto=KZ.RecID
CS> WHERE KO.Konto_Type=1
CS> AND B.Deleted='0' AND B.BDatum<='27.02.2005'

CS> in other words, when I have in restrictions something from joined 
CS> tables? where is this written?

CS> bye


CS> Claudius


Actually, I'm also much more closer to the Sybase community :)
But here is two possible forms of join query (following SQL-92)
1)
----------
Select
      ...
FROM
    T1, T2
WHERE
     T1.f1 = T2.f2
----------
2)
----------
Select
      ...
FROM
    T1 inner join T2 on T1.f1 = T2.f2
----------

It might be nice till we have deal with 2-table join.
But for many-tabled-join it is not so attractive to write (and read later!)
such quasi-English sentences. (As you know, the main SQL goal was to have something
like plain english text.)

IMHO it must be your decission what way do you like.

About performance.
I don't think you get notable difference.
But in second way it will be a little bit faster, because Valentina wouldn't parse
WHERE clause to find "join conditions". As for me I would use the way which I
used to.

Moreover, in you query, second way might seems a bit awful:

SELECT
      KO.Konto_Typ, KZ.Konto_Typ, B.Kategorie_ID, B.Betrag
FROM
    Konten KZ INNER JOIN
        (Buchungen B inner join Konten KO ON B.Herk_Konto=KO.RecID )
    ON B.Ziel_Konto=KZ.RecID
WHERE
     B.Deleted='0'
     AND B.BDatum<='27.02.2005'

-------------

Also, you should note that Valentina has some extensions here!
If single link exists between two tables you can use short forms for
inner joins - this link will be used automaticaly:

Persons                Addresses
--------               -----------
Name    ObjectPtr       RecID      City
------------------------------------------
Smith       3            1          NULL
Smith       4            2          Limerick
O'Konnor    2            3          NYC
                         4          Dublin
-------------------------------------------

--
select * from Persons, Addresses
--

Result:
Name    ObjectPtr       City
------------------------------------------
Smith       3            NYC
Smith       4            Dublin
O'Konnor    2            Limerick




                         

-- 
Best regards,
 Ivan                            mailto:IvanSmahin at public.kherson.ua