Solved

How to link to a table twice in MS SQL Server 2008

Posted on 2013-10-24
9
264 Views
Last Modified: 2013-10-28
Can you link to a table twice in SSMS

I get an error of obvious origin .....table named twice.

      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE
            ON ROSS_CUSTOMER.CM_SELLZONE = ROSS_IDCODE.ID_FIELD)
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE
            ON ROSS_PRODUCTS.PRD_A_GLTYPE = ROSS_IDCODE.ID_FIELD)

That is what I need to do. Can anyone tell me how to do it or is it even possible.?
0
Comment
Question by:ruavol2
  • 5
  • 2
  • 2
9 Comments
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 250 total points
ID: 39598620
Just give each instance of the table a unique alias:

 INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE1
            ON ROSS_CUSTOMER.CM_SELLZONE = ROSS_IDCODE1.ID_FIELD)
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE2
            ON ROSS_PRODUCTS.PRD_A_GLTYPE = ROSS_IDCODE2.ID_FIELD)
0
 

Author Comment

by:ruavol2
ID: 39598736
The minute I put that in the headers returned but the records did not.
I am going to assume that the links must be off since that is the only thing I know to check even though the fields are correct in the links.
Could the data be off....what else to check....?
0
 

Author Comment

by:ruavol2
ID: 39598782
I think I got it LEFT OUTER JOIN. That seems to have brought back all the records.
0
 

Author Comment

by:ruavol2
ID: 39598817
Will that bring back a boatload of duplicates.....?

Would using SELECT DISTINCT help in this case. Here is what I put in.
FROM
     CHLROS05.RAMSDB.dbo.ROSS_SALESHDR ROSS_SALESHDR 
	INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER ROSS_CUSTOMER 
		ON ROSS_SALESHDR.OH_CUSTOMER_NUMBER = ROSS_CUSTOMER.CM_CUSTKEY
    INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL ROSS_SALESDTL 
		ON ROSS_SALESHDR.OH_TICKET_NUMBER = ROSS_SALESDTL.OD_TICKET_NUMBER
    INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS ROSS_PRODUCTS 
		ON ROSS_SALESDTL.OD_PRODUCT = ROSS_PRODUCTS.PRD_PRODUCT
	LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE1
        ON ROSS_CUSTOMER.CM_SELLZONE = ROSS_IDCODE1.ID_FIELD
    LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE2
        ON ROSS_PRODUCTS.PRD_A_GLTYPE = ROSS_IDCODE2.ID_FIELD 
	INNER JOIN WebSnakDS_SNYB_test.dbo.RAMS_CrossRef RAMS_CrossRef
		ON ROSS_CUSTOMER.CM_CHAINID = RAMS_CrossRef.RAMS_CHAIN_NO

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 250 total points
ID: 39598831
Using distinct might work, but it would be treating the symptom instead of the disease.  By this I mean you most likely have a problem with one or more of the joins elsewhere, but without knowing the structure of your data I can't readily say what it might be.  But I am glad the unique aliases worked for you on that one table.
0
 

Author Comment

by:ruavol2
ID: 39598835
Wait.....I screwed up. It is supposed to be just one IDCode Table and two others linking to it.

Customer and Product link to one IDCODE table. See image
VLE-IDCode.png
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39599582
You could join the same table twice (using different aliases) but I think you may be able to do this. Viewing some data from ross_products, ross_customer and ross_idcode might help by the way you decide which way to go:
FROM CHLROS05.RAMSDB.dbo.ROSS_SALESHDR ROSS_SALESHDR
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER ROSS_CUSTOMER
                ON ROSS_SALESHDR.OH_CUSTOMER_NUMBER = ROSS_CUSTOMER.CM_CUSTKEY
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL ROSS_SALESDTL
                ON ROSS_SALESHDR.OH_TICKET_NUMBER = ROSS_SALESDTL.OD_TICKET_NUMBER
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS ROSS_PRODUCTS
                ON ROSS_SALESDTL.OD_PRODUCT = ROSS_PRODUCTS.PRD_PRODUCT

        LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE
                ON ROSS_CUSTOMER.CM_SELLZONE = ROSS_IDCODE.ID_FIELD
                OR ROSS_PRODUCTS.PRD_A_GLTYPE = ROSS_IDCODE.ID_FIELD

        INNER JOIN WebSnakDS_SNYB_test.dbo.RAMS_CrossRef RAMS_CrossRef
                ON ROSS_CUSTOMER.CM_CHAINID = RAMS_CrossRef.RAMS_CHAIN_NO

Open in new window

But, before you start joining all those tables - and then deciding you have "duplicates" that you will remove by the mystical power of "distinct" - consider this:

It is quite possible that your will get "unwanted repetition" from those joined tables -particularly if you are only choosing a few columns (and the reason for the repetition isn't therefore made visible). As we don't know which columns you need it's not possible to rule out any tables - but the first rule is don't join a table if you don't need it. Another approach is to look at it a different way, if you are needing ROSS_IDCODE data should it be the FROM table? And, then there are other approaches like summarizing tables using group by and joining those as subqueries instead of the base tables.

"distinct" is a "row operator", it is performed after the from/where clauses, it considers the entire row (all values of all rows) to arrive at unique rows - and this can be very a expensive operation. i.e. DISTINCT adds a workload to your query - it is not "free" (or magic) see:
http://webbtechsolutions.com/2009/07/24/the-effects-of-distinct-in-a-sql-query/
DISTINCT is *usually* bad

A good rule of thumb -- if you need a distinct list of values in a single column or two, DISTINCT is the way to go.  But if your results "don't look right" or you see some duplicate rows and can't figure out why, do NOT just add DISTINCT to your SELECT to "fix it" !!  Step back, look at your joins, and re-write your query properly.  Even worse, I've seen people simply add DISTINCT to all their SELECT's right from the start, to preemptively "avoid duplicates".  Not good.  ...
and more extreme: Why I Hate DISTINCT
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39599654
btw: "Viewing some data from ross_products, ross_customer and ross_idcode might help by the way you decide which way to go:"

what I really meant by this is to discover what id_field actually stores in ross_idcode

e.g.
select distinct top 20 ID_FIELD from ROSS_IDCODE ;
select distinct top 20 CM_SELLZONE  from ROSS_CUSTOMER;
select distinct top 20 PRD_A_GLTYPE from ROSS_PRODUCTS;

'top 20' is arbitrary, but the intent is to learn should I join twice, or can I use OR?
are the values from query 2 and 3 unique to each table or are they common to both.

(& yes, I have used distinct here, but this is not what I was discussing above)
0
 

Author Closing Comment

by:ruavol2
ID: 39606101
Perfect. Thanks. Changed the Join to LEFT OUTER, Got rid of the DISTINCT. and utilized the OR function.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

9 Experts available now in Live!

Get 1:1 Help Now