PostgreSQL JOIN Query with Aliases using capital letters not working

Question

I'm using C# Npgsql library to make some calls and operations into a PostgreSQL Database (the one from Supabase).

I know that Supabase has it's own SDK, but I can't use it.

So far so good all my queries and methods work, but I cannot figure out how to make this one work:

public ChapterQuestion Read(ChapterQuestion itemToRead, List<QuestionOption> questionOptions)
{
    ChapterQuestion chapterQuestion = null;

    var id = itemToRead.Id;
    var subjectId = itemToRead.SubjectId;
    var chapterId = itemToRead.ChapterId;
    var title = itemToRead.Title;
    var answerValue = itemToRead.AnswerValue;
    var shortTitle = title.Length > 20 ? title[..20] : title;

    // Verificar si existe el mismo registro
    string subjectIdString = (subjectId == null) ? $"WHERE @subjectId IS NULL AND " : $"WHERE \"subjectId\" = @subjectId AND ";
    string chapterIdString = (chapterId == null) ? $"@chapterId IS NULL AND " : $"\"chapterId\" = @chapterId AND ";
    string questionOptionDescriptionsString = string.Join(", ", questionOptions.Select(qo => $"'{qo.Description.Replace("'", "''")}'"));

    string selectQuery = $@"
        SELECT DISTINCT question.* 
        FROM {TABLE_CHAPTER_QUESTION} AS question
        JOIN {SupabaseQuestionOptionRepository.TABLE_QUESTION_OPTION} AS qo ON question.id = ""qo.chapterQuestionId""
        {subjectIdString}
        {chapterIdString}
        title = @title AND
        ""answerValue"" = @answerValue
        AND qo.description IN ({questionOptionDescriptionsString})";

    using (NpgsqlCommand selectCommand = new NpgsqlCommand(selectQuery, Connection))
    {
        selectCommand.Parameters.AddWithValue("@subjectId", subjectId);
        selectCommand.Parameters.AddWithValue("@chapterId", chapterId);
        selectCommand.Parameters.AddWithValue("@title", title);
        selectCommand.Parameters.AddWithValue("@answerValue", answerValue);

        var result = selectCommand.ExecuteScalar();

        if (result != null)
        {
            using NpgsqlDataReader reader = selectCommand.ExecuteReader();
            if (reader.Read())
            {
                int idOrdinal = reader.GetOrdinal("Id");
                int chapterQuestionId = reader.GetInt32(idOrdinal);
                LoggerService.Log($"The '{nameof(ChapterQuestion)}' record with the title '{shortTitle}' and subjectId '{subjectId}' and chapterId '{chapterId}' and answerValue '{answerValue}', exists with ID {chapterQuestionId}");
                chapterQuestion = new ChapterQuestion(chapterQuestionId, itemToRead);
            }
            else
            {
                LoggerService.Log($"The '{nameof(ChapterQuestion)}' record with the title '{shortTitle}' and subjectId '{subjectId}' and chapterId '{chapterId}' and answerValue '{answerValue}' was not found.");
                reader.Close();
            }
            CloseConnection();
            return chapterQuestion;
        }
        else
        {
            LoggerService.Log($"The '{nameof(ChapterQuestion)}' record with the title '{shortTitle}' and subjectId '{subjectId}' and chapterId '{chapterId}' and answerValue '{answerValue}' was not found.");
        }
    }

    CloseConnection();

    return chapterQuestion;
}

The error that I received is:

column "qo.chapterQuestionId" does not exist.

Triggered by the line:

JOIN {SupabaseQuestionOptionRepository.TABLE_QUESTION_OPTION} AS qo ON question.id = ""qo.chapterQuestionId""

I can confirm that the column exists in my Database:

AFAIK I need to use " " to indicate that this value is case sensitive, I've tried without them and the message turns into:

column qo.chapterquestionid does not exist.

I also tried to remove the "AS" when using the aliases from the query, but it doesn't affect anything.

Why is the database indicating that the column does not exist, if it does? How can I make the query to work properly?

Because indeed there is no column "qo.chapterQuestionId". You are looking for qo."chapterQuestionId". — Adrian Klaver
– Adrian Klaver, Commented Mar 18, 2024 at 21:53
Thanks, you're right, feel free to add it as an answer so I can mark it please — Lotan
– Lotan, Commented Mar 19, 2024 at 21:29
I already covered this here stackoverflow.com/questions/69176720/…. — Adrian Klaver
– Adrian Klaver, Commented Mar 19, 2024 at 21:39

Simon Perepelitsa · Accepted Answer · 2024-03-20 14:38:43Z

0

As already answered in comments, you need to quote the column name part only:

qo."chapterQuestionId"

The part before the dot is a table reference or alias. You can quote it as well if you need it to be case-sensitive:

"QO"."chapterQuestionId"

Only include the dot if it is part of the column name:

qo."chapterQuestion.id"

answered Mar 20, 2024 at 14:38

Simon Perepelitsa

20.7k8 gold badges58 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL JOIN Query with Aliases using capital letters not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related