Linking two tables/ Looking up data in one table

ashcarrot · June 24th, 2003, 03:01 PM

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

Jeff Mason · June 24th, 2003, 03:09 PM

Your "specification" isn't too clear, but it sounds like what you need is an OUTER JOIN:

Code:

SELECT details,first_name,last_name
  FROM report
  LEFT JOIN hr ON report.locked_by = hr.usrid;

This will return NULL for the 'first_name' and 'last_name' columns if there is no corresponding entry in the 'hr' table.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

ashcarrot · June 24th, 2003, 04:07 PM

Thanks thats done it :O)