none
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:
    SELECT
        MAX(a.name) as AuthorName,
        LEN(MAX(cast(f.beschreibung as varchar(max))))/4*COUNT(DISTINCT(f.id)) as Points,
        COUNT(DISTINCT f.id) as Count,
        MAX(a.avatarsource) as AvatarSource
    FROM author 
    INNER JOIN files f
       ON f.AutorId=a.id
    WHERE f.aktiviert=1 AND f.CreateDate > @FirstDayInMonth
    GROUP BY a.id
    ORDER BY Count desc


    What I figured out is:

    var temp = from a in this.author
    join f in this.files on new { AutorId = a.id } equals new { AutorId = f.AutorId }
    where f.aktiviert == 1 
    group a by new
    {
       a.id
    } into g
                           //orderby
    select new
    {
        AuthorName = g.Max(p => p.name),
        //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

Answers

  • 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 a.id

     

    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 a.id 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