asked on

Joining multiple tables

Hi all

The query below is not doing what it suppose to do..
The problem lies here = T919_ShortAccount T919b
I need to be able to join this table 2 times..

How can i achive this

SELECT T91500.ID AS InterfaceID, T91500.Account as MyAccountID,
                                          T91500.customerNr as CusNumer, T91500.name as buyerName,
                                          T91500.Place as custPlace,
                                          T91700.Description as HouseDescription,
                                                      T91800.Validfrom as FromDate,
                                                      T91800.validUntil as ToDate,
                                                      T91800.MajorCust as PremierCustorm
                                          FROM                   T91500_Custormer T915
                                          LEFT JOIN       T918_Account T918
                                                ON                   T915.ID = T918.AccountId
                                          LEFT JOIN       T919_ShortAccount T919
                                                ON                  T915.ID = T919.AccountID
                                          LEFT JOIN       T917_Organization T917
                                                ON                  T918.Account = T917.ID
                                          LEFT JOIN            T919_ShortAccount T919b
                                                ON                  T919b.AccountID = T917.ID

ASKER CERTIFIED SOLUTION

Andrew Crofts

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

you already join it twice

LEFT JOIN T919_ShortAccount T919
...
LEFT JOIN T919_ShortAccount T919b

whats the issue?

ZURINET

ASKER

The issue is this

Depending on which join comes first.. i.e

LEFT JOIN T917_Organization T917
ON T918.Account = T917.ID

LEFT JOIN T919_ShortAccount T919b
ON T919b.AccountID = T917.ID

If T918.Account comes first Value of T919b will be missing

if T919b.AccountID comes first Values of T918.Account will be missing..

Strange.. !

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rajesh_mj

Please try below:

SELECT T91500.ID AS InterfaceID, T91500.Account as MyAccountID,
T91500.customerNr as CusNumer, T91500.name as buyerName,
T91500.Place as custPlace,
T91700.Description as HouseDescription,
T91800.Validfrom as FromDate,
T91800.validUntil as ToDate,
T91800.MajorCust as PremierCustorm
FROM T91500_Custormer T915
LEFT JOIN T918_Account T918
ON T915.ID = T918.AccountId
LEFT JOIN T919_ShortAccount T919
ON T915.ID = T919.AccountID
LEFT JOIN T917_Organization T917
ON T918.Account = T917.ID
LEFT JOIN T919_ShortAccount T919b
ON T917.ID = T919b.AccountID

ZURINET

ASKER

The solution to this problem is to use union all instead of left join

Andrew Crofts

Thanks