How to use column that get from select to use it in another select that using each data in column as-CodePudding

I want to use column from this select:

select A from table_1

To use it as where condition in this select:

select * from table_2 where B = table_1.A

I’m interested in proving my sql skills with script writing so I want to do it with for loops. Can I do it with for loop like that without making new tables in base:

begin
for i in (select A from table_1) 
     loop
    select * from table_2 where B = i;
    end loop;
end;

Or it will be not possible to make this without creating a new tables?

My script below gives me error: PLS-00428 an INTO clause is expected in this SELECT statement

Can it be possible that the only right way to do this task is use this kind of select:

select t2.* from table_1 t1, table_2 t2 where t1.A = t2.B

It would be great to try solve this task in different ways

CodePudding user response：

As Oracle says, a select needs its into. If it (select) returns only one row, you can use scalar variables. If not, use a collection.

This example is based on Scott's sample schema and displays some information about employees in their departments.

SQL> set serveroutput on
SQL>
SQL> declare
  2    type    l_row    is table of emp%rowtype;
  3    l_tab   l_row;
  4  begin
  5    for i in (select deptno from dept) loop   --> this is your 1st query
  6      select *                                --> this is your 2nd query
  7        bulk collect
  8        into l_tab
  9        from emp e
 10        where e.deptno = i.deptno;
 11
 12      for j in 1 .. l_tab.count loop
 13        dbms_output.put_line('Department ' || i.deptno ||': ' || l_tab(j).ename ||', '|| l_tab(j).job);
 14      end loop;
 15    end loop;
 16  end;
 17  /
Department 10: CLARK, MANAGER
Department 10: KING, PRESIDENT
Department 10: MILLER, CLERK
Department 20: SMITH, CLERK
Department 20: JONES, MANAGER
Department 20: SCOTT, ANALYST
Department 20: ADAMS, CLERK
Department 20: FORD, ANALYST
Department 30: ALLEN, SALESMAN
Department 30: WARD, SALESMAN
Department 30: MARTIN, SALESMAN
Department 30: BLAKE, MANAGER
Department 30: TURNER, SALESMAN
Department 30: JAMES, CLERK

PL/SQL procedure successfully completed.

SQL>

Depending on what you really have, that code might need to be adjusted, but - that's the general idea.

CodePudding user response：

Don't use PL/SQL, just use a SELECT:

SELECT *
FROM   table_2 t2
WHERE  EXISTS (
  SELECT 1
  FROM   table_1 t1
  WHERE  t1.A = t2.B
);

or:

SELECT *
FROM   table_2
WHERE  B IN (SELECT A FROM table_1);

You can use:

SELECT t2.*
FROM   table_2 t2
       INNER JOIN table_1 t1
       ON (t1.A = t2.B)

or, using your query, with the legacy comma-join syntax:

select t2.* from table_1 t1, table_2 t2 where t1.A = t2.B

However, these will return duplicate rows if there are multiple rows in table_1 that match a row in table_2 and that is probably not what you want.