get info from several tables

[danwiking]

Hi

I have following script using 3 diff. tables

select distinct '"' ||
@left(u.christian_name,30) || '"' || ' ' || '"' ||
@left(u.surname,30) || '"' || ' ' || '"' ||
@left(e.extension,20) || '"' || ' ' || '"' ||
@left(u.organisation,30) || '"' || ' ' || '"' ||
msg.msgaddress || '"' || ' ' || '"' ||
@left(u.mobilephone,30) || '"'

from users u, extensions e, MSG_SYSTEMS msg
where u.userid=e.userid and u.userid = msg.userid(+) and (msg.MSGSYSTEMID = 6 or msg.MSGSYSTEMID IS NULL)
order by u.userid;

this one works fine.

now i want to have some extra information added to the result from another table called subjects.

the values in this table are userid and subject

and i want a result even when the msg_systems and subjects table are empty for a userid.

something like this

select distinct '"' ||
@left(u.christian_name,30) || '"' || ' ' || '"' ||
@left(u.surname,30) || '"' || ' ' || '"' ||
@left(e.extension,20) || '"' || ' ' || '"' ||
@left(u.organisation,30) || '"' || ' ' || '"' ||
msg.msgaddress || '"' || ' ' || '"' ||
s.subject || '"' || ' ' || '"' ||
@left(u.mobilephone,30) || '"'

from users u, subjects s, extensions e, MSG_SYSTEMS msg
where u.userid=e.userid and u.userid = msg.userid and (msg.MSGSYSTEMID = 6 or msg.MSGSYSTEMID IS NULL) and u.userid=s.userid(+)
order by u.userid;

but the problem here are that there can be then one subject for each userid in the subjects table - but it should only return the first one.

does any one have a idea

thnks