Solved

Joining multiple tables

Posted on 2011-02-22
7
265 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:ZURINET
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 13

Accepted Solution

by:
Philip Pinnell earned 260 total points
ID: 34952624
Use different aliases

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 T919b1
                                                ON                  T919b.AccountID = T917.ID
                                          LEFT JOIN            T919_ShortAccount T919b2
                                                ON                  T919b.whatever= T917.whateverID

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34952719
you already join it twice

  LEFT JOIN         T919_ShortAccount T919
...
  LEFT JOIN         T919_ShortAccount T919b

whats the issue?
0
 

Author Comment

by:ZURINET
ID: 34952814
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.. !
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 240 total points
ID: 34952912
try this

FROM              T91500_Custormer T915
LEFT JOIN         T918_Account T918            ON T918.AccountId = T915.ID
LEFT JOIN         T917_Organization T917       ON T917.ID = T918.Account
LEFT JOIN         T919_ShortAccount T919a      ON T919a.AccountID = T915.ID
LEFT JOIN         T919_ShortAccount T919b      ON T919b.AccountID = T917.ID
0
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 34958246
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
0
 

Author Closing Comment

by:ZURINET
ID: 34994735
The solution to this problem is to use union all instead of left join
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 34995748
Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now