Solved

Joining multiple tables

Posted on 2011-02-22
7
310 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 51

Expert Comment

by:Huseyin KAHRAMAN
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

762 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