Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Joining multiple tables

Posted on 2011-02-22
7
Medium Priority
?
327 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 780 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 61

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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 720 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

885 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