Query with grouped results by column (SQL Server)

Question

I've got a many to many relationship between items-itemnames-languages

The itemnames don't appear for every language.

I'd like to get a result with all the items only represented once, but be able to set the languageId to default to.

For example items 1,2,3 are defined in two languages, and item 4 and 5 have one language each, but the languages are different

[itemid][languageid][name]
1,       1,         item1
1,       2,         leItem1
2,       1,         item2
2,       2,         leItem2
3,       1,         item3
3,       2,         leItem3
4,       1,         item4
5,       2,         leItem5

I'd like to create a query that only gives me one of each itemID, but allow me to specify which language to prefer, so if I select a languageID of 2, my query would only return item names for that start with 'leItem' with the exception of item 4, which should still give me item4

Any ideas how to achieve this with a SELECT?

The theory (how I'm trying to get it to work), is that I create two queries, one for all where languageID matches x , and second where each item is represented (by group or distinct) and then merge the results.

---

sql for generating the table

-- Languages
CREATE TABLE [Languages] (
    [id] INT NOT NULL PRIMARY KEY IDENTITY,
    [language] NVARCHAR(20) NOT NULL ,
    [languagecode] NVARCHAR(6) NOT NULL
);


-- Items
CREATE TABLE [Items] (
    [id] INT NOT NULL PRIMARY KEY IDENTITY,
    [ImageId] INT ,
    [lastupdate] DATETIME,
    [isactive] BIT NOT NULL DEFAULT 'TRUE'
);

-- ItemNames
CREATE TABLE [ItemNames] (
    [itemId] INT NOT NULL ,
    [languageId] INT NOT NULL ,
    [name] NVARCHAR(50) NOT NULL ,
    FOREIGN KEY (itemId) REFERENCES Items(id),
    FOREIGN KEY (languageId) REFERENCES Languages(id),
    PRIMARY KEY ([itemId],[languageId])
);

Answer 1

You might be able to solve this with a subquery. This might not be the best way way of solving it, but I'll take a stab at it.

SELECT DISTINCT
  outer.itemid, 
  outer.languageid, 
  outer.name 
FROM 
  table AS outer
WHERE 
  outer.languageid = 2  
  OR NOT EXIST (SELECT * FROM table AS inner WHERE languageid = 2 AND inner.itemid = outer.itemid)

The first where condition is to provide all the entries that belongs to languageid = 2, the second part of the where conditions return true only if there isn't an entry with languageid = 2 for the current item you're looking at.

http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

Answer 2

I would try using a CASE statement. Basically the idea is to determine if the preferred language exists and return null if it does not. That way you can utilize ISNULL to returned the preferred language (when it is populated) otherwise return the minimum language ID:

// replace @preferred with the preferred language id ie 2
SELECT  itn.ItemID, itn.LanguageID, itn.Name
FROM    ItemNames itn INNER JOIN 
        (
            SELECT  itemID, 
                    // use preferred language if it exists
                    // otherwise, use the minimum languageID
                    ISNULL( MIN ( CASE WHEN LanguageID = @preferred THEN LanguageID ELSE NULL END ) 
                                , MIN (LanguageID) 
                          ) AS LanguageID
            FROM   ItemNames 
            GROUP BY itemID
        )
        sel ON sel.ItemID = itn.ItemID AND sel.LanguageID = itn.LanguageID 

Answer 3

this is how I'm handling it for now...

I've created a cffunction that loops through the query repeatedly. It works, and because I'm sub-querying, I think performance should be ok. Seems like using a database side query might be too difficult for me.

<cffunction name="getLanguageUniqueEntries" output="no" returntype="query" >
    <cfargument name="q" Type="query" required="true">
    <cfargument name="languageId" Type="string" required="true">
    <cfargument name="uniqueColumn" Type="string" required="false" default="ID">

    <!---  Copy structure, assume table has an id column where no item matches -1 --->
    <cfquery dbtype="query" name="newQ"> SELECT * FROM q WHERE #uniqueColumn# = -1 </cfquery>

    <!--- get list of unigue IDs --->
    <cfquery dbtype="query" name="uniquePropertyIDs"> SELECT #uniqueColumn# FROM q GROUP BY #uniqueColumn# </cfquery>

    <!--- loop through unique IDs ---->
    <cfloop query="uniquePropertyIDs">
        <cfset colIdVal = uniquePropertyIDs[uniqueColumn][uniquePropertyIDs.CurrentRow]>

        <!--- find row in language --->
        <cfquery dbtype="query" name="currentLangQ" maxrows="1">
            SELECT * FROM q WHERE #uniqueColumn# = #colIdVal# AND LanguageID = #languageId#
        </cfquery>

        <cfif currentLangQ.recordcount NEQ 0>
            <!--- insert row --->
            <cfquery dbtype="query" name="newQ"> SELECT * FROM newQ UNION SELECT * FROM currentLangQ </cfquery>
        <cfelse>
            <!--- entry in language not found, get a default value --->
            <cfquery dbtype="query" name="anyLangQ" maxrows="1"> SELECT * FROM q WHERE #uniqueColumn# = #colIdVal# ORDER BY LanguageID</cfquery>

            <!--- insert row --->
            <cfquery dbtype="query" name="newQ"> SELECT * FROM newQ UNION SELECT * FROM anyLangQ</cfquery>
        </cfif>

    </cfloop>
    <cfreturn newQ>
</cffunction>