Printing data from sub-queries SQL-CodePudding

I have 2 tables: SalesPeople and Customers that have snum and cnum as primary key respectively; both tables have city column as well.

Without using joins, we have to tell the names of customers and salespeople that belong to same city.

I have used nested queries to print the salespeople that belong to the city of customers, but cant figure out how to print customer names with this .

SELECT S.* 
FROM SalesPeople S
WHERE City IN(
    SELECT City 
    FROM Customers CX
    );

CodePudding user response：

How about this? (Disregard the fact that the WITH factoring clause doesn't exist in Oracle 9i (at least, I think so); you already have those tables).

Sample data:

SQL> with
  2  salespeople (snum, city) as
  3    (select 1, 'London' from dual union all
  4     select 2, 'Paris'  from dual union all
  5     select 3, 'Rome'   from dual
  6    ),
  7  customers (cnum, city) as
  8    (select 100, 'Zagreb' from dual union all
  9     select 101, 'Rome'   from dual union all
 10     select 102, 'Rome'   from dual union all
 11     select 103, 'Paris'  from dual
 12    )

Query:

 13  select person_num
 14  from (select snum as person_num, city from salespeople
 15        union
 16        select cnum, city from customers
 17       )
 18  where city = 'Rome';

PERSON_NUM
----------
         3
       101
       102

SQL>