Solved

Joining multiple tables

Posted on 2011-02-22
7
313 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 53

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

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Agent Timeout 5 74
configure service broker on all databases 2 99
How to simplify my SQL statement? 14 61
Urgent help needed! Unable to to paste in query designer 29 57
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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