Oracle Query Problem

[caskey]

I am having difficulty trying to put together a query (Oracle 8i) that would give me all records from one table and the corresponding records from another table in which the value is specified. I have tried various subqueries with left joins etc and haven't had any luck.

For example, list all names (Table1) and the specified codes (Table2) where value = S

Table1
id name
1 A
2 B
3 C
4 D
5 E

Table2
id value code
1 R X
1 S Y
2 S Z
2 T Y
3 U X
4 S Y


Desired Results: (known value = S)
name code
A Y
B Z
C
D Y
E


Any help would be appreciated...

 

[sem]

select a.name, b.code
from table1 a, table2 b
where b.value(+)='S' and b.id(+)=a.id

 

[KALYANIDRV]

Hi caskey,

Please find herewith attaching the solution regarding your problem.

solution 1:-
----------

SELECT A.NAME,B.CODE FROM TABLE1 A,(SELECT DISTINCT ID,VALUE,CODE FROM TABLE2 WHERE VALUE = 'S') B
WHERE A.ID = B.ID(+);

(or)

SOLUTION :- 2
--------

SELECT A.NAME,B.CODE FROM TABLE1 A ,TAB1 B WHERE A.ID = B.ID AND
B.VALUE = 'S'
UNION
SELECT A.NAME,NULL FROM TABLE2 A, TAB1 B WHERE A.ID <> B.ID

Thanks,
Ravi.

 

[caskey]

Many thanks for all the great answers! All three work as expected. Is there a consensus as to which one may be the most efficient? When executing the code against my actual data, I find the UNION to be the slowest and the other two to be very comparable...