Solved

Joining multiple tables

Posted on 2011-02-22
7
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 56

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
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.

 
LVL 56

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

635 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