how to exclude a column from displaying in SQL-CodePudding

I have three tables. I want to select columns from two of them to display but in WHERE clause I want to use the third table as well e.g.

SELECT table1.colTABLE1, table2.colTABLE2, 
FROM table1, table2 
WHERE table3.colTABLE3 > 10000;

It means I want to view the columns of two tables based on the condition applied on the column of the third table.

Now the problem is, if I do not write the table3 in FROM clause, it gives me error of "invalid identifier". If I write table3 in FROM clause, it gives warning that you have not used this table (But I used the table3 in WHERE clause). Please help me in this. I am really confused. How can I display them properly with no error or warning?

CodePudding user response：

There is no warning from Oracle if you use a table in the FROM clause and none of its columns appears in the SELECT clause. That must be caused by whatever client tool you are using.

You have to list all tables in the FROM clause, but you also have to join them. After all, you don't want all rows from table1 combined with all rows from table2 and table3.

An example query could look like this:

SELECT table1.col1, table1.col2,
       table2.col3, table2.col4
FROM table1
   JOIN table2
      ON table1.a = table2.b
   JOIN table3
      ON table1.x = table3.y
WHERE table3.col > 10000;

You will have to figure out what the join conditions are. Often they are defined by a foreign key.

CodePudding user response：

The warning only appears in the SQL Developer worksheet, if you hover over the table3 table name, which is underlined to indicate a possible problem. It doesn't affect the query execution, and you can just ignore it. (It also says that the table "is not used in the select clause", which is true; not that it isn't used at all.)

If you really don't want to see the warning then you can turn off the 'Semantic Analysis Info Tips' feature; from tools->Preferences->Code Editor->Completion Insight, untick the 'Enable Semantic Analysis Info Tip' checkbox and click OK.

(In SQL Developer 22.2 at least, the squiggle won't disappear immediately; but if you change and revert the table name then it won't reappear.)

But that will remove all of those tips, not just this specific warning.

You can also go to Tools->Preferences->Code Editor->PL/SQL Syntax Colors, find 'Disconnected Join Graph' (obviously!?) in the list, and untick 'Enable Highlight'. That will stop the yellow squiggle appearing under the table name, for this and other similar warnings; but the tooltip will still appear if you hover over the table name. You just won't have the visual cue that there is a message to look for.