SQL Join Question

[joero4]

I need to join to tables of data. I have one table of chemical sample results and another table of chemicals. I need to join the tables in such a way that for all the chemical sample results I get the chemical, but I also want all the other chemical even if there is no result for that chemical.

SAMPLE_RESULTS
-----------------------
chemical_id
sample_result

CHEMICALS
-----------------------
chemical_id
chemical_name

I want to see...
---------------------------------
CARBON 2
CADMIUM 3
IRON
OXYGEN

Please help,
THANK YOU

 

[jimbopalmer]

Select c.chemical_name, sr.sample_result from chemicals c, sample_results sr
where c.chemical_id = sr.chemical_id (+)
order by c.chemical_id;

(I am assuming carbon has a smaller ID than cadmium since it is not alphabetical order)

I tried to remain child-like, all I acheived was childish.

 

[sem]

select chemical_name, sample_result
from SAMPLE_RESULTS r, CHEMICALS c
where r.chemical_id(+)=c.chemical_id


For more details read about outer joins.

Regards, Dima