Linking tables

[ashcarrot]

Hi,

I have two tables

REPORT - hold details about a report and who has that locked (blank if its not locked)
HR - Hold name, and id

REPORT(details,locked_by)
HR(usrid,firstname,lastname)

now if i just use

SELECT details,first_name,last_name FROM report,hr where report.locked_by = hr.usrid

it will just display those that are locked(have data in the locked_by field)

Though i need it to display the report but just have blank for the first and last name if its not in there

Any ideas? i figure this should be easy i just can't work it out

Thanks
Ash

 

[Turkbear]

Hi,
Use a Left Outer Join:

One way ( v8i and before

SELECT details,first_name,last_name FROM report,hr where report.locked_by = hr.usrid(+)

New way ( v9i)

SELECT details,first_name,last_name FROM report LEFT OUTER JOIN hr ON (report.locked_by = hr.usrid)

These both say give me all the records in report even if no match is made in hr

 

[ashcarrot]

Thanks thats done it :O)