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
);
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>