Solved

How to make this query ?

Posted on 2012-03-30
15
437 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

696 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