Solved

How to make this query ?

Posted on 2012-03-30
15
436 Views
Last Modified: 2012-08-14
Hi!

Have this query

SELECT
    dbo.KJE_FLINKTOTAL.MKjede1,
    dbo.KJE_KJEDE.Kjedenavn,
    dbo.KJE_KJEDE.Kjedenummer,
    dbo.KJE_FLINKTOTAL.RKjede1
FROM
    dbo.KJE_FLINKTOTAL
INNER JOIN
    dbo.KJE_KJEDE
ON
    (
        dbo.KJE_FLINKTOTAL.MKjede1 = dbo.KJE_KJEDE.Kjedenummer)
AND (
        dbo.KJE_FLINKTOTAL.RKjede1 = dbo.KJE_KJEDE.Kjedenummer)
INNER JOIN
    dbo.KJE_KJEDEREG
ON
    (
        dbo.KJE_FLINKTOTAL.LoepeNr = dbo.KJE_KJEDEREG.Lopenummer) ;

Open in new window


Need to get the dbo.KJE_KJEDE.Kjedenavn from this JOIN
dbo.KJE_FLINKTOTAL.RKjede1 = dbo.KJE_KJEDE.Kjedenummer

The problem is that dbo.KJE_KJEDE.Kjedenavn is used on this JOIN
dbo.KJE_FLINKTOTAL.MKjede1 = dbo.KJE_KJEDE.Kjedenummer

If i try to insert dbo.KJE_KJEDE.Kjedenavn after dbo.KJE_FLINKTOTAL.RKjede1
i get the same dbo.KJE_KJEDE.Kjedenavn as for dbo.KJE_FLINKTOTAL.MKjede1


How can i fix this ?
0
Comment
Question by:team2005
[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
  • 5
  • 3
  • +1
15 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37786481
<<The problem is that dbo.KJE_KJEDE.Kjedenavn is used on this JOIN
dbo.KJE_FLINKTOTAL.MKjede1 = dbo.KJE_KJEDE.Kjedenummer>>

Don't see KJE_KJEDE.Kjedenavn in that comparison.

<<If i try to insert dbo.KJE_KJEDE.Kjedenavn after dbo.KJE_FLINKTOTAL.RKjede1
i get the same dbo.KJE_KJEDE.Kjedenavn as for dbo.KJE_FLINKTOTAL.MKjede1>>
It's both same table, so the join always will be for same records, the order does not matter.
If you want different joins for both conditions then you must join the same table twice
FROM    dbo.KJE_FLINKTOTAL as t
INNER JOIN     dbo.KJE_KJEDE as k1
ON    (       t.MKjede1 = k1.Kjedenummer
          and   k1.Kjedenavn = ??????? )
INNER JOIN     dbo.KJE_KJEDE as k2
on        t.RKjede1 =k2.Kjedenummer

Open in new window

0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37786486
I'm not getting what your exact problem is, could you rephrase a little?

Are you trying to get the Kjedenavn from a different table?  The way the field is referenced in the SELECT, dbo.KJE_KJEDE.Kjedenavn, includes the table name (dbo.KJE_KJEDE).  If you need the same field from FlinkTotal then you should add the following to your SELECT:

dbo.KJE_FLINKTOTAL.Kjedenavn
0
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37786497
Use an alias:

INNER JOIN dbo.KJE_KJEDE KK2
ON dbo.KJE_FLINKTOTAL.MKjede1 = KK2.Kjedenummer

and Select KK2.Kjedenummer

(KK2 is an alias for your table)
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:team2005
ID: 37786564
Hi!

I wil try to explane better.

Here is the query i want:

SELECT
    dbo.KJE_FLINKTOTAL.MKjede1,
    dbo.KJE_KJEDE.Kjedenavn 'Navn1',
    dbo.KJE_KJEDE.Kjedenummer,
    dbo.KJE_FLINKTOTAL.RKjede1,
    dbo.KJE_KJEDE.Kjedenavn 'Navn2',
    dbo.KJE_KJEDEREG.Lopenummer,
    dbo.KJE_FLINKTOTAL.LoepeNr,
    dbo.KJE_KJEDEREG.Besoksadresse,
    dbo.KJE_KJEDEREG.postnrbesok,
    dbo.KJE_KJEDEREG.Postadresse,
    dbo.KJE_KJEDEREG.Postnrpost,
    dbo.KJE_FLINKTOTAL.RKjede2,
    dbo.KJE_KJEDE.Kjedenavn 'Navn3'
FROM
    dbo.KJE_FLINKTOTAL
INNER JOIN
    dbo.KJE_KJEDE
ON
    (
        dbo.KJE_FLINKTOTAL.MKjede1 = dbo.KJE_KJEDE.Kjedenummer)
AND (
        dbo.KJE_FLINKTOTAL.RKjede1 = dbo.KJE_KJEDE.Kjedenummer)
AND (
        dbo.KJE_FLINKTOTAL.RKjede2 = dbo.KJE_KJEDE.Kjedenummer)
INNER JOIN
    dbo.KJE_KJEDEREG
ON
    (
        dbo.KJE_FLINKTOTAL.LoepeNr = dbo.KJE_KJEDEREG.Lopenummer) ;

Open in new window


If you lock at the included picture.

MKjede1, RKjede1 and RKjede2 is JOIN to the same field in KJEDE
= dbo.KJE_KJEDE.Kjedenummer

The result from the query is that

Navn2 = Navn1
Navn3 = Navn1

I want :

Navn1 -> Get from dbo.KJE_FLINKTOTAL.MKjede1 = dbo.KJE_KJEDE.Kjedenummer
Navn2 -> Get from dbo.KJE_FLINKTOTAL.RKjede1 = dbo.KJE_KJEDE.Kjedenummer
Navn3 -> Get from dbo.KJE_FLINKTOTAL.RKjede2 = dbo.KJE_KJEDE.Kjedenummer

But how ?
tables.png
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37786588
try this:

select
      FT.MKjede1,
    MK1.Kjedenavn 'Navn1',
    MK1.Kjedenummer,
    FT.RKjede1,
    RK1.Kjedenavn 'Navn2',
    RK1.Lopenummer,
    FT.LoepeNr,
    REG.Besoksadresse,
    REG.postnrbesok,
    REG.Postadresse,
    REG.Postnrpost,
    FT.RKjede2,
    RK2.Kjedenavn 'Navn3'
FROM dbo.KJE_FLINKTOTAL FT
INNER JOIN dbo.KJE_KJEDE MK1 on MK1.Kjedenummer = FT.MKjede1
INNER JOIN dbo.KJE_KJEDE RK1 on RK1.Kjedenummer = FT.RKjede1
INNER JOIN dbo.KJE_KJEDE RK2 on RK2.Kjedenummer = FT.RKjede2
INNER JOIN dbo.KJE_KJEDEREG REG ON FT.LoepeNr = REG.Lopenummer

By joining the same table multiple times, each time using a different foreign key, you can access the records in the other table (KJE_KJEDE) in each context.
0
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37786597
SELECT
    dbo.KJE_FLINKTOTAL.MKjede1,
    dbo.KJE_KJEDE.Kjedenavn 'Navn1',
    dbo.KJE_KJEDE.Kjedenummer,
    dbo.KJE_FLINKTOTAL.RKjede1,
    dbo.KK2.Kjedenavn 'Navn2',
    dbo.KJE_KJEDEREG.Lopenummer,
    dbo.KJE_FLINKTOTAL.LoepeNr,
    dbo.KJE_KJEDEREG.Besoksadresse,
    dbo.KJE_KJEDEREG.postnrbesok,
    dbo.KJE_KJEDEREG.Postadresse,
    dbo.KJE_KJEDEREG.Postnrpost,
    dbo.KJE_FLINKTOTAL.RKjede2,
    dbo.KJE_KJEDE.Kjedenavn 'Navn3'
FROM dbo.KJE_FLINKTOTAL
INNER JOIN dbo.KJE_KJEDE 
ON (dbo.KJE_FLINKTOTAL.MKjede1 = dbo.KJE_KJEDE.Kjedenummer)
AND (dbo.KJE_FLINKTOTAL.RKjede1 = dbo.KJE_KJEDE.Kjedenummer)
INNER JOIN dbo.KJE_KJEDEREG ON (dbo.KJE_FLINKTOTAL.LoepeNr = dbo.KJE_KJEDEREG.Lopenummer) 
INNER JOIN dbo.KJE_KJEDE KK2
ON (dbo.KJE_FLINKTOTAL.RKjede2 = KK2.Kjedenummer) 

Open in new window

0
 
LVL 2

Author Comment

by:team2005
ID: 37786652
Hi!

Tryed this:

select
      FT.MKjede1,
    MK1.Kjedenavn 'Navn1',
    MK1.Kjedenummer,
    FT.RKjede1,
    RK1.Kjedenavn 'Navn2',
    RK1.Lopenummer,
    FT.LoepeNr,
    REG.Besoksadresse,
    REG.postnrbesok,
    REG.Postadresse,
    REG.Postnrpost,
    FT.RKjede2,
    RK2.Kjedenavn 'Navn3'
FROM dbo.KJE_FLINKTOTAL FT
INNER JOIN dbo.KJE_KJEDE MK1 on MK1.Kjedenummer = FT.MKjede1
INNER JOIN dbo.KJE_KJEDE RK1 on RK1.Kjedenummer = FT.RKjede1
INNER JOIN dbo.KJE_KJEDE RK2 on RK2.Kjedenummer = FT.RKjede2
INNER JOIN dbo.KJE_KJEDEREG REG ON FT.LoepeNr = REG.Lopenummer

Open in new window


Get this erro message:
Error Code: 207, SQL State: 42S22]  Invalid column name 'Lopenummer'.

Tryed to change REG.Lopenummer = dbo.KJE_KJEDEREG

Give this error message:
[Error Code: 4104, SQL State: S1000]  The multi-part identifier "dbo.KJE_KJEDEREG.Lopenummer" could not be bound. 2) [Error Code: 207, SQL State: 42S22]  Invalid column name 'Lopenummer'.


Then i tryed this code:

SELECT
    dbo.KJE_FLINKTOTAL.MKjede1,
    dbo.KJE_KJEDE.Kjedenavn 'Navn1',
    dbo.KJE_KJEDE.Kjedenummer,
    dbo.KJE_FLINKTOTAL.RKjede1,
    dbo.KK2.Kjedenavn 'Navn2',
    dbo.KJE_KJEDEREG.Lopenummer,
    dbo.KJE_FLINKTOTAL.LoepeNr,
    dbo.KJE_KJEDEREG.Besoksadresse,
    dbo.KJE_KJEDEREG.postnrbesok,
    dbo.KJE_KJEDEREG.Postadresse,
    dbo.KJE_KJEDEREG.Postnrpost,
    dbo.KJE_FLINKTOTAL.RKjede2,
    dbo.KJE_KJEDE.Kjedenavn 'Navn3'
FROM dbo.KJE_FLINKTOTAL
INNER JOIN dbo.KJE_KJEDE 
ON (dbo.KJE_FLINKTOTAL.MKjede1 = dbo.KJE_KJEDE.Kjedenummer)
AND (dbo.KJE_FLINKTOTAL.RKjede1 = dbo.KJE_KJEDE.Kjedenummer)
INNER JOIN dbo.KJE_KJEDEREG ON (dbo.KJE_FLINKTOTAL.LoepeNr = dbo.KJE_KJEDEREG.Lopenummer) 
INNER JOIN dbo.KJE_KJEDE KK2
ON (dbo.KJE_FLINKTOTAL.RKjede2 = KK2.Kjedenummer) 

Open in new window


Gives me this error message:

 [Error Code: 4104, SQL State: S1000]  The multi-part identifier "dbo.KK2.Kjedenavn" could not be bound.
0
 
LVL 8

Accepted Solution

by:
gpizzuto earned 500 total points
ID: 37786676
Delete dbo. before KK2
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37786720
Sorry, copy/paste issue in SELECT list, here's the corrected version:

select
      FT.MKjede1,
    MK1.Kjedenavn 'Navn1',
    MK1.Kjedenummer,
    FT.RKjede1,
    RK1.Kjedenavn 'Navn2',
    REG.Lopenummer,
    FT.LoepeNr,
    REG.Besoksadresse,
    REG.postnrbesok,
    REG.Postadresse,
    REG.Postnrpost,
    FT.RKjede2,
    RK2.Kjedenavn 'Navn3'
FROM dbo.KJE_FLINKTOTAL FT
INNER JOIN dbo.KJE_KJEDE MK1 on MK1.Kjedenummer = FT.MKjede1
INNER JOIN dbo.KJE_KJEDE RK1 on RK1.Kjedenummer = FT.RKjede1
INNER JOIN dbo.KJE_KJEDE RK2 on RK2.Kjedenummer = FT.RKjede2
INNER JOIN dbo.KJE_KJEDEREG REG ON FT.LoepeNr = REG.Lopenummer
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 37786727
Working yust great.
Thank you
0
 
LVL 2

Author Comment

by:team2005
ID: 37786819
Hi!

Sorry, Navn3 is still = Navn1

CREATE VIEW KJE_EXPORT_PREVIEW AS
SELECT
    dbo.KJE_FLINKTOTAL.MKjede1,
    dbo.KJE_KJEDE.Kjedenavn 'Navn1',
    dbo.KJE_KJEDE.Kjedenummer,
    dbo.KJE_FLINKTOTAL.RKjede1,
    KK2.Kjedenavn 'Navn2',
    dbo.KJE_KJEDEREG.Lopenummer,
    dbo.KJE_FLINKTOTAL.LoepeNr,
    dbo.KJE_KJEDEREG.Besoksadresse,
    dbo.KJE_KJEDEREG.postnrbesok,
    dbo.KJE_KJEDEREG.Postadresse,
    dbo.KJE_KJEDEREG.Postnrpost,
    dbo.KJE_FLINKTOTAL.RKjede2,
    dbo.KJE_KJEDE.Kjedenavn 'Navn3'
FROM dbo.KJE_FLINKTOTAL
INNER JOIN dbo.KJE_KJEDE 
ON (dbo.KJE_FLINKTOTAL.MKjede1 = dbo.KJE_KJEDE.Kjedenummer)
AND (dbo.KJE_FLINKTOTAL.RKjede1 = dbo.KJE_KJEDE.Kjedenummer)
INNER JOIN dbo.KJE_KJEDEREG ON (dbo.KJE_FLINKTOTAL.LoepeNr = dbo.KJE_KJEDEREG.Lopenummer) 
INNER JOIN dbo.KJE_KJEDE KK2
ON (dbo.KJE_FLINKTOTAL.RKjede2 = KK2.Kjedenummer) 

Open in new window

0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37786859
Please try the updated query which I posted in comment 37786720.
0
 
LVL 2

Author Comment

by:team2005
ID: 37786898
Hi!

That one works fine :)
I must split the points here, but how ?
0
 
LVL 25

Expert Comment

by:jogos
ID: 37786916
Use the table alias you use for the join also and always in your select, like in the example of ValentinoV

dbo.KJE_KJEDE.Kjedenavn 'Navn3'
must be
kk2.Kjedenavn

PS: the accepted sollution was just a little modification on other script .... that started from first post 'If you want different joins for both conditions then you must join the same table twice' + example
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37786943
team2005: cool!  You probably should use the Request Attention button to revise your closure.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

740 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