Right Syntax Select Join Group RRS feed

  • Question

  • I have 2 Tables:
    author (int id,string name)
    files (int id, int authorid, string beschreibung) // Beschreibung => Description in English

    Ich have the following SQL Statement:
        MAX( as AuthorName,
        LEN(MAX(cast(f.beschreibung as varchar(max))))/4*COUNT(DISTINCT( as Points,
        COUNT(DISTINCT as Count,
        MAX(a.avatarsource) as AvatarSource
    FROM author 
    INNER JOIN files f
    WHERE f.aktiviert=1 AND f.CreateDate > @FirstDayInMonth
    ORDER BY Count desc

    What I figured out is:

    var temp = from a in
    join f in this.files on new { AutorId = } equals new { AutorId = f.AutorId }
    where f.aktiviert == 1 
    group a by new
    } into g
    select new
        AuthorName = g.Max(p =>,
        //Points = (System.Int64?)((int?)(g.Max(p => Convert.ToString(p.Beschreibung))).Length / 4 * 5),    // <- Autocomplete does not show p.Beschreibung, WHY ?
        AvatarSource = g.Max(p => p.AvatarSource),

    My QUestion ist: What about the Points? How can I assign a value from a joined table to this column. I can only assign values from the author table, but not from the files table. IDE says: Containts no definition for Beschreibung. AutoComplete does not show columns from the "files " table. Just from the "author" table.

    Please help
    Friday, August 28, 2009 1:36 PM


  • Hi regaa,


    Your problem is caused by the group member. You just group by a. So you can’t get the property form f.

    You can modify like this:

    Group new{a, f} by


    Then you will get the property in the last select like this:

    p=>p.f.XXX,  p=>p.a.XXX


    Another suggestion is that you can just use “join XXX on equals f.AutorId”. Use new will effect the performance if it is not necessary.


    Best Regards

    Yichun Feng


    • Marked as answer by Yichun_Feng Thursday, September 3, 2009 2:45 AM
    Monday, August 31, 2009 8:36 AM