Solved

How to make this query ?

Posted on 2012-03-30
15
431 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now