March 3, 2008 at 5:36 am
Hello,
I should create a view that returns a name value 3times but in one single line
the table looks like this
Table ADRES:
ADR_ID
ADR_NAAM
Table SCHIP
SCHIP_ID
REDER_ID
OWNER_ID
SCHIPPER_ID
LND_ID
Table LAND
LND_ID
LAND_DETAIL
the columns in SCHIP (REDER_ID, OWNER_ID, SCHIPPER_ID) are all linked on ADR_ID in table ADRES
So the view should RETURN the name value for each of those id's provided by a SCHIP but on a single line as REDER_NAME, OWNER_NAME, SCHIPPER_NAME
Also is the SCHIP linked on a table LAND for witch i also have to get some data back
to provide more info i present hereby some data:
ADRES
ADR_ID = 1 ADR_NAME = Eddy
ADR_ID = 5 ADR_NAME = Poll
ADR_ID = 8 ADR_NAME = Brian
SCHIP:
SCHIP_ID = 80
REDER_ID = 1
OWNER_ID = 5
SCHIPPER_ID = 8
LAND_ID = 9
Whereby the view should return
Schip_id : 80, Reder_NAME=Eddy, OWNER_NAME= Poll, SCHIPPER_NAME = Brian, DETAIL = LAND_DETAIL
Can anyone help me out with this though query
Wkr,
Eddy
March 3, 2008 at 7:01 am
Hi Guys,
I have managed to come to a solution in the following way,
Can any expert please advice me if this is the right way to do this,
SELECT dbo.ADRES.ADR_NAAM AS EigenaarNaam, tbl_Schipper.ADR_NAAM AS Schippernaam, tbl_Reder.ADR_NAAM AS RederNaam, dbo.LAND.LND_CDE2,
dbo.LAND.LND_CDE3
FROM dbo.ADRES INNER JOIN
dbo.SCHIP ON dbo.ADRES.ADR_ID = dbo.SCHIP.SCH_EIGENAAR_ID INNER JOIN
dbo.ADRES AS tbl_Schipper ON dbo.SCHIP.SCH_SCHIPPER_ID = tbl_Schipper.ADR_ID INNER JOIN
dbo.ADRES AS tbl_Reder ON dbo.SCHIP.SCH_REDER_ID = tbl_Reder.ADR_ID LEFT OUTER JOIN
dbo.LAND ON dbo.SCHIP.LND_ID = dbo.LAND.LND_ID
Tnx for any feedback
March 3, 2008 at 7:18 am
try this...
select SCHIP_ID
, (select ADR_NAAM from dbo.ADRES where ADR_ID = dbo.SCHIP.REDER_ID) as Reder_NAME
, (select ADR_NAAM from dbo.ADRES where ADR_ID = dbo.SCHIP.OWNER_ID) as OWNER_NAME
, (select ADR_NAAM from dbo.ADRES where ADR_ID = dbo.SCHIP.SCHIPPER_ID) as SCHIPPER_NAME
from dbo.SCHIP
----------------------------------------------
--OR
------------------------------------------------
select
SCHIP_ID
, max(Case when REDER_ID = ADR_ID then ADR_NAAM else '0' end) as Reder_NAME
, max(Case when OWNER_ID = ADR_ID then ADR_NAAM else '0' end) as OWNER_NAME
, max(Case when SCHIPPER_ID = ADR_ID then ADR_NAAM else '0' end) as SCHIPPER_NAME
from dbo.SCHIP
,dbo.ADRES
group by
SCHIP_ID, REDER_ID, OWNER_ID, SCHIPPER_ID
March 3, 2008 at 7:42 am
eddy (3/3/2008)
Hi Guys,I have managed to come to a solution in the following way,
Can any expert please advice me if this is the right way to do this,
SELECT dbo.ADRES.ADR_NAAM AS EigenaarNaam, tbl_Schipper.ADR_NAAM AS Schippernaam, tbl_Reder.ADR_NAAM AS RederNaam, dbo.LAND.LND_CDE2,
dbo.LAND.LND_CDE3
FROM dbo.ADRES INNER JOIN
dbo.SCHIP ON dbo.ADRES.ADR_ID = dbo.SCHIP.SCH_EIGENAAR_ID INNER JOIN
dbo.ADRES AS tbl_Schipper ON dbo.SCHIP.SCH_SCHIPPER_ID = tbl_Schipper.ADR_ID INNER JOIN
dbo.ADRES AS tbl_Reder ON dbo.SCHIP.SCH_REDER_ID = tbl_Reder.ADR_ID LEFT OUTER JOIN
dbo.LAND ON dbo.SCHIP.LND_ID = dbo.LAND.LND_ID
Tnx for any feedback
Looks fine Eddy! However, I'd use left joins in case some of the values are missing, and some small changes for readability if you like...
SELECT
sc.Schip_id,
e.ADR_NAAM AS EigenaarNaam,
s.ADR_NAAM AS Schippernaam,
r.ADR_NAAM AS RederNaam,
l.LND_CDE2,
l.LND_CDE3
FROM dbo.SCHIP sc
LEFT JOIN dbo.ADRES e
ON e.ADR_ID = sc.SCH_EIGENAAR_ID
LEFT JOIN dbo.ADRES s
ON s.ADR_ID = sc.SCH_SCHIPPER_ID
LEFT JOIN dbo.ADRES r
ON r.ADR_ID = sc.SCH_REDER_ID
LEFT OUTER JOIN dbo.LAND l
ON sc.LND_ID = l.LND_ID
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 3, 2008 at 8:47 am
Hello Tony,
I find your code much easier to read and maintain but have some questions about it,
If you enclose the select into a max( ) statement, can the query optimalizer still make use of the index that sits on ADRES.PAR_ID ?
this because ive read somewhere that scalar functions do block index seeks,
anyhow, tnx for the response, i found it verry usefull
Ps: in my code, is that what they do call "derived tables" or is that still something else ..?
wkr
Eddy
March 3, 2008 at 9:16 am
Looks like Index on ADRES will not be used.
March 3, 2008 at 9:42 am
if you want to hit index try this
Create function func1(@adrID smallint)
returns table as
return
(
select ADR_NAAM from dbo.ADRES
where ADR_ID = @adrID
)
select
SCHIP_ID
, (Select ADR_NAAM from func1(REDER_ID)) as Reder_NAME
, (Select ADR_NAAM from func1(OWNER_ID)) OWNER_NAME
, (Select ADR_NAAM from func1(SCHIPPER_ID)) as SCHIPPER_NAME
from dbo.SCHIP
Thanks
March 3, 2008 at 10:25 am
Hello tony,
Can i include (or create) views based on functions .??
I already noticed that the possibility of including stored procedures is not possible...
Chriss, tnx for the tip on the left join, seems idd that i really needed to include that on,
cause some ships can have te possibility of not having a link in the adres table..
March 3, 2008 at 10:37 am
hi
I am not sure of that. I have never done that.
Thanks
March 5, 2008 at 10:31 am
eddy (3/3/2008)
Hello tony,Can i include (or create) views based on functions .??
I already noticed that the possibility of including stored procedures is not possible...
Chriss, tnx for the tip on the left join, seems idd that i really needed to include that on,
cause some ships can have te possibility of not having a link in the adres table..
Yes, you absolutely can use functions. Even Table-Valued ones.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply