[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-10-24
9
Medium Priority
?
275 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 1000 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

649 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