Ta ut reserv om inte önskad finns

4 svar · 582 visningar · startad av Dino 10 okt. 2006

DinoMedlem sedan sep. 20011 914 inlägg

Frågan10 okt. 2006 10:27#1

SqlServer 2005

    CREATE TABLE [Application]
    (
        [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
        [Title] [nvarchar] (50) NOT NULL
    )
    
    CREATE TABLE [Language]
    (
        [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
        [TwoLetterName] [char] (2)  NOT NULL,
        [Name] [nvarchar] (50) NOT NULL
    )
    
    CREATE TABLE [LanguageInApplication]
    (
        [LanguageId] [int],
        [ApplicationId] [int,
        [IsDefault] [bit]
    )
    
    INSERT INTO [Application](Title) VALUES('App 1');
    
    INSERT INTO [Language](TwoLetterName, [Name]) VALUES('sv', 'Svenska');
    INSERT INTO [Language](TwoLetterName, [Name]) VALUES('en', 'Engelska');
    INSERT INTO [Language](TwoLetterName, [Name]) VALUES('es', 'Spanska');
    INSERT INTO [Language](TwoLetterName, [Name]) VALUES('fr', 'Franska');
    
    INSERT INTO [LanguageInApplication](LanguageId, ApplicationId, IsDefault) VALUES(1, 1, 1);
    INSERT INTO [LanguageInApplication](LanguageId, ApplicationId, IsDefault) VALUES(1, 2, 0);
    INSERT INTO [LanguageInApplication](LanguageId, ApplicationId, IsDefault) VALUES(1, 3, 0);
    INSERT INTO [LanguageInApplication](LanguageId, ApplicationId, IsDefault) VALUES(1, 4, 0);
    
    CREATE PROCEDURE dbo.Application_GetSomething 
    (
        @appname varchar(50),
        @language char(2)
    )
    AS
        /*
        Vill ta ut Application.Name och Language.Name 
        där Application.Name = @appname och Language.TwoLetterName = @language.  
        Om Language.TwoLetterName inte finns så skall den ta den 
        Language.Name som har LanguageInApplication.IsDefault
        */

LarsGMedlem sedan dec. 200012 464 inlägg

10 okt. 2006 17:13#2

 select title,
        coalesce(name,
                 (select name
                    from language
                   where languageId in
                  (select languageid
                     from languageInApplication
                    where isDefault = 1
                      and applicationId in
                          (select id
                             from application
                            where title = @appname))
    from application
    left join languageApplication on application.id = languageApplication.applicationid
    left join language on languageApplication.languageID = language.id
     and language.twoLetterName = @language

DinoMedlem sedan sep. 20011 914 inlägg

10 okt. 2006 17:37#3

Nja. Det skall bara retunera 1 rad din retunerar alla Applikationer.

Verkar väldigt invecklad oxå, hade något som nedan men den retunerar inget om @language är en tom sträng eller inte har ett värde som är samma som de i Language.

SELECT     a.Name as AppName, l.Name as LanguageName, 
FROM        dbo.Application a INNER JOIN 
                dbo.LanguageInApplication lia ON a.Id = lia.ApplicationId INNER JOIN 
                        dbo.Language l ON lia.LanguageId = l.Id                    
WHERE      a.Name = @appname  
                AND  
                ( 
                        l.TwoLetterName = @language  
                        OR  
                        ( 
                                lia.IsDefault = 1 
                                AND NOT EXISTS 
                                ( 
                                        SELECT	l1.Id 
                                        FROM	dbo.Language l1 
                                        WHERE	l1.TwoLetterName = @language 
                                ) 
                        ) 
                );

Är ovan helt fel?

LarsGMedlem sedan dec. 200012 464 inlägg

10 okt. 2006 17:57#4

Jag glömde villkoret

where title = @appname

som skall läggas sist i den yttre selectfrågan.

DinoMedlem sedan sep. 20011 914 inlägg

10 okt. 2006 18:12#5

Tror jag fått till det själv. Tackar för din hjälp iaf.

SELECT     a.Name as AppName, l.Name as LanguageName, 
FROM        dbo.Application a INNER JOIN 
                dbo.LanguageInApplication lia ON a.Id = lia.ApplicationId INNER JOIN 
                        dbo.Language l ON lia.LanguageId = l.Id                    
WHERE      a.Name = @appname  
                AND  
                ( 
                        l.TwoLetterName = @language  
                        OR  
                        ( 
                                lia.IsDefault = 1 
                                AND NOT EXISTS 
                                ( 
                                        [B]SELECT    l1.Id 
                                        FROM      dbo.Application a1 INNER JOIN 
                                                        dbo.LanguageInApplication lia1 ON a1.Id = lia1.ApplicationId INNER JOIN 
                                                            dbo.Language l1 ON lia1.LanguageId = l1.Id  
                                        WHERE	a1.Name = @appname AND l1.TwoLetterName = @language [/B] 
                                ) 
                        ) 
                );