Missing from clause entry for table ошибка

SELECT 
   AcId, AcName, PldepPer, RepId, CustCatg, HardCode, BlockCust, CrPeriod, CrLimit, 
   BillLimit, Mode, PNotes, gtab82.memno 
FROM
   VCustomer AS v1
INNER JOIN   
   gtab82 ON gtab82.memacid = v1.AcId 
WHERE (AcGrCode = '204' OR CreDebt = 'True') 
AND Masked = 'false'
ORDER BY AcName

You typically only use an alias for a table name when you need to prefix a column with the table name due to duplicate column names in the joined tables and the table name is long or when the table is joined to itself. In your case you use an alias for VCustomer but only use it in the ON clause for uncertain reasons. You may want to review that aspect of your code.

Missing from clause entry for table error statement doesn’t target only a query with a missing table name. You’ll find a variety of reasons leading you to the same error.

But there isn’t anything to worry about as long as you have this post opened on your screen because it will provide you with the much-needed guidance to fix the error instantly. Keep reading to discover the reasoning and solution parts for the error stated above.

Missing From Clause Entry for Table Error: Causes Described

The missing from clause entry for table error is caused due to skipping the table name in your query, using the where and join clauses interchangeably, or not using the duplicating option in Sequelize. Also, the lack of support to join tables for update queries in Knex can be problematic.

– You Haven’t Specified the Table Name for Your Operation

If you don’t specify the table name in the from clause while running a database query, you’ll encounter the missing from-clause entry for table Rails error. It is because the postgreSQL queries require the table name to complete your desired database operation.

For example, you want to select all of the columns from the employee table. Therefore, you are trying to run the select statement with the * asterisk sign that represents all columns. But you forget to specify the table name in the from clause. Here, the above error will pop up on your screen, complaining about the missing table name.

You can see the erroneous select statement below:

Note that even if you specify an incorrect table name, the same error will occur. So, you can say consider the following select statement problematic too because it targets employees table instead of the employee table.

– You Are Getting Confused Between the Where and Join Clauses

Not having clarity about the usage of where and join clauses can put you in trouble by directing the given error to your system. You might be trying to work with two tables simultaneously without using the join clause while relying on the where clause only.

Say that you want to select the designation column from table A based on a condition specified in the where clause. But the condition makes use of table A and table B. Now, if you use both of the tables in the where clause to create a condition without joining them, you’ll see the above error.

– The Duplicating Option in Sequelize

The duplicating option can be set to true or false. You can say that you can switch it on or off, but if you turn it on or off at the wrong time, the missing from-clause entry for table Sequelize error will land on your PC.

In case you don’t know, the duplicating option allows you to duplicate values while working with nested include queries. The nested include queries make it possible for you to join three tables where the third table is related to the second table.

– You Haven’t Used the references() Method With the Includes

The missing from-clause entry for table active record might occur when you don’t use the references() method with the includes. This way, the table specified in includes doesn’t join the string specified in the where clause leading to a failed query execution.

You must know that the references() method tells that the specified table must not be loaded separately. Instead, it should be joined in the SQL string for flawless results. Here you go with an erroneous code:

User.includes(:students).where(“students.name = ‘John’”)

– Knex Won’t Allow You To Join Data for Update Queries

Knex doesn’t allow you to join tables to execute the update queries. Therefore, if you attempt to join data for update queries, the missing from-clause entry for table Knex will start flashing on your screen. So, you can mark the inefficiency of Knex to be the culprit.

However, the lack of support for the stated operation in Knex doesn’t make it a bad query builder because it has its good sides too.

Missing From Clause Entry for Table Error: How To Fix?

You can fix the missing from clause entry for table error by mentioning the correct table name in the from or join clause or using the where and join clauses wisely. Moreover, setting the duplicating option in Sequelize or executing individual table update queries or subqueries in Knex will help.

– Mention the Correct Table Name in the From Clause

You should never miss specifying the table name in the from clause to fix the missing from-clause entry for table GORM error. Plus, it would be best to check the spelling of the table name to ensure that you type it correctly in the from clause.

So, the simplest of the solutions to resolve the missing from-clause entry for table typeORM error is to stay right with your table name. You can use the following query to select all of the columns from the employee table:

– Specify the Table Name in the Join Clause At least

If you are selecting multiple columns from two different tables, then you should specify one table name in the from clause while stating another table name in the join clause. This way, you’ll satisfy the query expectations and fix the error occurring on your PC.

Say that you want to select the grade column from the student table and the subject column from the teacher table. In such a situation, your query must look like the one given below to achieve your desired results:

SELECT student.grade, teacher.subject from student INNER JOIN teacher on student.s_ID = teacher.s_ID

– Use the Join and Where Clauses Wisely

Understanding the purpose and usage of the join and where clauses can help you use the said clauses wisely and lower the chances of the given error. You should know that the where clause is used to specify a condition, while the join clause is used to join two tables.

Therefore, you can’t use them interchangeably. Moreover, sometimes, you might need to use the WhereHas clause for writing and executing complex queries in Laravel. The said clause will make things easier for you by applying the where condition on your has queries and help you remove the missing from-clause entry for table Laravel error.

– Turn On or Off the Duplicating Option in Sequelize

It is recommended to turn on the duplicating option in Sequelize for all the child include statements to get rid of the error stated in the title. All that you’ll have to do is to the set duplicating:true for each nested include statement.

Also, you should note that sometimes, duplicating:false sorts out the problem too. So, you might like to check which option works in your favor.

– Leverage the references() Method With Includes

It would be best to leverage the references() method with includes to ensure that the table name specified in the includes joins with the SQL string to create a sensible query. Consequently, the error will quickly disappear from your screen.

Targeting the example shared earlier, all that you’ll have to do is to append the references() method to your code for an error-free execution. Here is the corrected code:

User.includes(:students).where(“students.name = ‘John’”).references(:students)

– Execute Separate Update Queries or Go for the Subquery Concept

If you are using Knex, then you should skip the joining data or tables part and go for separate update query executions to push away the error. Also, you might like to create a subquery inside your main update query to ensure that Knex is able to execute it.

In short, staying away from joining tables to update your data in Knex is considered a good practice and will be super helpful in avoiding the given error.

Conclusion

As per this post, you need to have a clear understanding of the different SQL clauses to write complete and valid queries. Moreover, you might find working on PGAdmin or related tools easier to use than the ORMs, and it will consequently help you to avoid the missing from-clause entry for table SQLAlchemy error. Lastly, go through the following listicle to wrap up the guide.

• You should enter the table name in the from or join clause to ensure the completeness of your query.
• Using the references() method with the includes will allow joining the table name in the where clause string to resolve the error.
• Never join tables for update queries while using Knex to maintain distance from the said error.
• Switch on the duplicating option for the child include queries in Sequelize to get the error fixed.
• Never use the where and join clauses interchangeably.

Please feel free to use this article to figure out the problems in your queries and code and have an error-free database management experience.

В FROM нужно указать таблицу teterika.users и условие связи с таблицей teterika.lessons.
если условие не указать, то свяжется каждая строка одной таблицы с каждой строкой другой таблицы, получится декартово произведение таблиц.

SELECT 
  tl.subject,
  tu.role
FROM teterika.lessons AS tl, teterika.users AS tu
WHERE teterika.users.role = 'tutor'

Кстати, ваш запрос не имеет смысла, потому что из teterika.users.role у вас извлечется только запись ‘tutor’ в соответствии с условием. Т.е. ваш запрос можно заменить на:

SELECT 
  tl.subject,
  'tutor' AS role
FROM teterika.lessons

Разве что вам действительно нужно получить декартово произведение всех уроков со всеми учителями.

Hello Everyone.

@pleerock @Kononnable I think this could be possibly reopened.

I started facing the same issue today with TypeORM 0.2.24 using PostgreSQL driver when trying to fetch entities with @ManyToMany relation.

I don’t know why, but the generated query is missing the double quotes which seems to be the reason of missing FROM-clause entry for table errors.

Here is a simplified extract from my entities:

@Entity()
export class ProductOption {

    @ManyToMany(() => OptionValue, (value: OptionValue) => value.options)
    @JoinTable({ name: 'product_option_value' })
    public values: OptionValue[];
}

@Entity()
export class OptionValue {

    @Column({
        type: 'simple-json',
    })
    public value: { baseValue: string, alternativeValue: string }; // just an example

    @ManyToMany(() => ProductOption, (productOption: ProductOption) => productOption.values)
    public options: ProductOption[];
}

See I have both sides of ManyToMany relation defined with a custom-named @JoinTable.

Now, when I try to fetch Options with related OptionValues, e.g.

productOptionRepository.find({ relations: ["values"] });

I get this query generated and this FAILS:

query failed: SELECT ProductOption_values_rid.optionValueId AS «optionValueId», ProductOption_values_rid.productOptionId AS «productOptionId» FROM «option_value» «option_value» INNER JOIN «product_option_value» «ProductOption_values_rid» ON (ProductOption_values_rid.productOptionId = $1 AND ProductOption_values_rid.optionValueId = «option_value».»id») ORDER BY ProductOption_values_rid.optionValueId ASC, ProductOption_values_rid.productOptionId ASC — PARAMETERS: [123]

error: missing FROM-clause entry for table «productoption_values_rid»

Please note the bold parts — they are missing double quotes. I compared that to a different but similar relation in other part of my apps, and there the query includes double quotes! This is what I found in different sources can cause such missing FROM-clause entry for table errors.

@ishan123456789 provided me a hint to check if the simple-json may be causing this issue. So I removed the relation:

productOptionRepository.find();

and voliá! It magically works. There has to be something wrong with the query and/or pivot table being generated automatically by TypeORM for @ManyToMany relation when we have an entity containing simple-json column.

Any help kindly appreciated!