Solved

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

Posted on 2013-10-24
9
269 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
[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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

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 49

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 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