ORACLE SQL check if the row number of the table is n, then perform a join

oracle join multiple tables
oracle left outer join multiple tables
oracle sql join examples
oracle select from multiple tables without join
how to join two tables in plsql
pl sql join 3 tables
oracle full outer join
right outer join in oracle

TABLE student: ID, ID2, NAME, AGE

TABLE class: ID, CLASS_NAME, some other columns

TABLE school: ID2, some other columns.

I am trying to perform below in Oracle SQL:

If the count of the records in TABLE "student" with age>5, is 1, join the student table with "CLASS" table by "ID", else, join the student table with "school" table by "ID2".

I found I cannot put count in where clause. Can someone help?

I would use window functions:

select s.*, . . .
from (select s.*, sum(case when age > 5 then 1 else 0 end) over () as cnt5
      from students s
     ) s left join
     class c
     on c.id = s.id and cnt5 = 1 left join
     school sch
     on sch.id2 = s.id2 and cnt5 <> 1
where c.id is not null or sch.id is not null

Tutorial: Joining Tables: Databases for Developers, Whatever you do, choose one style and stick with it! Module 3. Cross Joins. A cross join returns every row from the first table  TABLE student: ID, ID2, NAME, AGE. TABLE class: ID, CLASS_NAME, some other columns. TABLE school: ID2, some other columns. I am trying to perform below in Oracle SQL: If the count of the records in TABLE "student" with age>5, is 1, join the student table with "CLASS" table by "ID", else, join the student table with "school" table by "ID2".

you can use left join with case

   select s.*, case when age > 5 then COALESCE (c.ID,scl.ID2) as id 
     from student s
    left join class c on s.ID=c.ID
    left join school scl on s.ID2=scl.ID2

Joins, Database SQL Reference A join is a query that combines rows from two or more tables, views, or materialized views. The columns in the join conditions need not also appear in the select list. If a query joins three or more tables and you do not specify a join condition for a specific pair, then the optimizer may choose a  Datadictionary tables like USER_TABLES in not reliable for row count. Because, even if the statistic is up to date, but after analyzing there may be some DML already altered the row number. A method to see the row count may be like SQL> SELECT 2 table_name, 3 to_number( 4 extractvalue(

As Gordon said window functions are an option, but this is also a rare occasion where you can deliberately perform a cross join:

select s.*
,...
from students s
left join
(
select count(*) as RECORD_COUNT
from students
where age > 5
) sub
on 1 = 1
left join class c
on s.id = c.id
and sub.record_count = 1
left join school h
on s.id2 = h.id2
and c.id is null

Fetching a Row Plus N Rows Either Side in a Single SQL Statement , Let's look at how you do in this in a single, efficient select statement. When constructing SQL queries it's always beneficial to have Using the classic employees table and N = 1, you could try a query like: select Oracle then applies lag and lead on just this one row - not all the rows in the underlying table. You use json_table in a SQL FROM clause. It is a row source: it generates a row of virtual-table data for each JSON value selected by a row path expression (row pattern). The columns of each generated row are defined by the column path expressions of the COLUMNS clause.

ROWNUM Pseudocolumn, Database SQL Reference the order in which Oracle selects the row from a table or set of joined rows. You can use ROWNUM to limit the number of rows returned by a query, as in this SELECT * FROM employees WHERE ROWNUM < 10;. If an ORDER BY clause follows ROWNUM in the same query, then the rows  ROW_NUMBER clause in Oracle SQL – PLSQL. The ROW_NUMBER clause in Oracle SQL / PLSQL is basically a windowing clause and is used to assign a unique row number to fetched records based on an ordered list. ROW_NUMBER clause starts numbering from 1. ROW_NUMBER clause can be used with and without PARTITION BY clause.

On ROWNUM and Limiting Results, A row in a table does not have a number; you cannot ask for row 5 from a And then run your top- N query with ROWNUM: SQL> select * 2 from 3 (select a. The result of a right outer join for table A and B contains all records of the right table B, even if the join condition does not match a row in the left table A. If no matching row from A exists, then A columns contain nulls for rows that have no match in A .

Optimization of Joins, Database SQL Language Reference If a query joins three or more tables and you do not specify a join condition for a specific pair, then the optimizer may  Before you do so, there is some tidying up steps needed. To prevent duplicating rows in the output, include a check that the computed recursion count (lvl) equals N. You also need to be wary of searching for rows near the start or end of the employee IDs. If recursion will take you beyond the max or min IDs, then the subquery will return null

Comments
  • That's a strange task. What is your result supposed to look like? You do know that the query must select the same number and types of columns in both cases, don't you? You can do something like select coalesce(class.class_name, school.school_name) as class_or_school_name, but is this really what you want? Please edit your request and show a sample result for both cases.