SQL left join with multiple rows into one row

Basically, I have two tables, Table A contains the actual items that I care to get out, and Table B is used for language translations.

So, for example, Table A contains the actual content. Anytime text is used within the table, instead of storing actual varchar values, ids are stored that relate back to text stored in Table B. This allows me to by adding a languageID column to Table B, have multiple translations for the same row in the database.

Example:

Table A

• Title (int)
• Description (int)
• Other Data....

Table B

• TextID (int) - This is the column whose value is stored in other tables
• LanguageID (int)
• Text (varchar)

My quest开发者_如何学Goion is more a call for suggestions on how to best handle this. Ideally I want a query that I can use to select from the table, and get the text as opposed to the ids of the text out of the table. Currently when I have two text items in the table this is what I do:

SELECT C.ID, C.Title, D.Text AS Description
FROM
(SELECT A.ID, A.Description, B.Text AS Title
FROM TableA A, TranslationsTable B
WHERE A.Title = B.TextID AND B.LanguaugeID = 1) C
LEFT JOIN TranslationsTable D
ON C.Description = D.TextID AND D.LanguaugeID = 1

This query gives me the row from Table A I am looking for (using where statements in the inner select statement) with the actual text based on the language ID used instead of the text ids.

This works fine when I am only using one or two text items that need to be translated, but adding a third item or more, it starts to get really messy - essentially another left join on top of the example.

Any suggestions on a better query, or at least a good way to handle 3 or more text items in a single row?

Per suggestions, I've added an example of the two tables:

    Table A  
    ---------------------------
    ID    |Title    |Description  
    ---------------------------  
    1     |1        |2  
    ---------------------------  
    2     |3        |4  
    --------------------------- 

    Table B (Translations Table) 
    ---------------------------
    ID    |LanguaugeID|Text  
    ---------------------------  
    1     |1        |Here is title one
    ---------------------------  
    1     |2        |Here is a title one in espanol
    ---------------------------
    2     |1        |Here is description one
    ---------------------------  
    2     |2        |Here is description one in espanol
    ---------------------------
    3     |1        |Title 2
    ---------------------------  
    4     |1        |Description 2
    ---------------------------   

What I want is to be able to pull a row out of table A that already has the text from table B, not just the ids - and to be able to do this for several columns that need translations.

It sounds like you want would benifit from converting some of the row data to column data. In that case look up the PIVOT functionality here

http://msdn.microsoft.com/en-us/library/ms177410.aspx

You could write a query to PIVOT out the text so that you can get the following output

ID, Title, Description, LanguageId, Text1, Text2, Text3, Text4

The only disadvantage to a PIVOT in TSQL is that you have to identify the number of pivot columns beforehand (when you write the query). You can, however, overcome this by writing dynamic SQL.

Look into using a function to return data for each column that requires translation. One could be:

CREATE FUNCTION dbo.fTranslate
    (@TextId int, @LanguageId int)
RETURNS nvarchar(100)  --  Should use nvarchar, and set to max length of string
AS
 BEGIN
    SELECT [Text]  --  Reserved wordin SQL, rename that column!
     FROM TableB
     WHERE TextId = @TextId
      And LanguageId = @LanguageId
 END

Then write the query as:

SELECT
  ID,
  dbo.fTranslate(Title, @LanguageId) Title,
  dbo.fTranslate(Description, @LanguageId) Description
 FROM TableA

This might not perform particularly well, as you have to call the function once for each translated column for each row returned (i.e. 3 columns for 100 rows = 300 function calls), but if you’re only returning one row at a time, it might not perform so poorly. Test it and be wary.

So TableA is basically a table with all foreign keys to TableB? You could create a view that included all of the joins to TableB. It would have columns for ID, LanguageID, TitleText, DescriptionText, etc. The view would have one row for each language so you could select a row-specific language by constraining LanguageID.

I do this with UNION of e.g. 3 different SELECT STATEMENTS (one for each language) where you provide empty columns for the language you are not working on, something like

SELECT A.ITM_ID, A.ITM_LOOKUPNAME, B.ITM_DESC_TEXT as TitleFR, B.ITM_DESC_UNIT AS UnitFR,'' as TitleEN, '' as UnitEN
FROM Item A, Item_description B
WHERE A.ITM_ID = B.ITM_ID AND B.ITM_DESC_LANG = 1

UNION

SELECT A.ITM_ID, A.ITM_LOOKUPNAME, '' as TitleFR, '' as UnitFR, B.ITM_DESC_TEXT as TitleEN, B.ITM_DESC_UNIT AS UnitEN
FROM Item A, Item_description B
WHERE A.ITM_ID = B.ITM_ID AND B.ITM_DESC_LANG = 2

You can then do a select with group function on the acquired set.

I think that a view is more appropriate here, as Jamie Ide said. In the following example, you can create LocalisedMessages and LocalisedArticles from the second query as views.

-- i18n.sql (TransactSQL)

create table #Languages(ID int, Code char(2), LanguageName varchar(32));
create table #_Messages(ID int, DefaultText nvarchar(1024));
create table #Translations(ID int, MessageID int, LanguageID int, TranslatedText nvarchar(1024));
create table #Articles(ID int, TitleID int, ContentID int);


insert into #Languages(ID, Code, LanguageName) values
    ( 1, 'en', 'English'  ),
    ( 2, 'es', 'Espagnol' );

insert into #_Messages(ID, DefaultText) values
    ( 1, 'Title 1'   ),
    ( 2, 'Content 1' ),
    ( 3, 'Title 2'   ),
    ( 4, 'Content 2' );

insert into #Translations
    ( ID, MessageID, LanguageID,  TranslatedText ) values
    (  1,         1,          1,  'Title 1'      ),
    (  2,         2,          1,  'Content 1'    ),
    (  3,         1,          2,  'Título 1'     ),
    (  4,         2,          2,  'Contenido 1'  ),
    (  5,         3,          1,  'Title 2'      ),
    (  6,         4,          1,  'Content 2'    ),
    (  7,         3,          2,  'Título 2'     ),
    (  8,         4,          2,  'Contenido 2'  );

insert into #Articles(ID, TitleID, ContentID) values
    ( 1, 1, 2 ),
    ( 2, 3, 4 );


select _m.ID, _m.DefaultText, _t.TranslatedText, _l.ID as LanguageID, _l.Code as LanguageCode
    from #_Messages _m
        inner join #Translations _t on _t.MessageID = _m.ID
        inner join #Languages _l on _l.ID = _t.LanguageID;


with LocalisedMessages as (
    select _m.ID, _m.DefaultText, _t.TranslatedText, _l.ID as LanguageID, _l.Code as LanguageCode
        from #_Messages _m
            inner join #Translations _t on _t.MessageID = _m.ID
            inner join #Languages _l on _l.ID = _t.LanguageID
    ),
LocalisedArticles as (
    select _a.ID, _l.Code as LanguageCode
        , isnull(_mt.TranslatedText, _mt.DefaultText) as Title
        , isnull(_mc.TranslatedText, _mc.DefaultText) as Content
        from #Articles _a
            inner join LocalisedMessages _mt on _mt.ID = _a.TitleID
            inner join LocalisedMessages _mc on _mc.ID = _a.ContentID
            inner join #Languages _l on _l.ID = _mt.LanguageID and _l.ID = _mc.LanguageID
    )
    select *
        from LocalisedArticles;


drop table #Articles;
drop table #Translations;
drop table #_Messages;
drop table #Languages;