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

x
?
Solved

How to make this query ?

Posted on 2012-03-30
15
Medium Priority
?
439 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

656 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