help with joins

[campkev]

Situation

table1 has 2 columns: userid and requiredInfo
It will have an entry for every user

table2 has 2 columns: userid and optionalInfo
It will have entries for some users but not others

Problem:
need a statement that returns either

userid, table1.requiredInfo, table2.optionalInfo
--if table2 has an entry
OR
userid, table1.requiredInfo, null
--if table2 does not have an entry

[stoefln]

Situation

table1 has 2 columns: userid and requiredInfo
It will have an entry for every user

table2 has 2 columns: userid and optionalInfo
It will have entries for some users but not others

Problem:
need a statement that returns either

userid, table1.requiredInfo, table2.optionalInfo
--if table2 has an entry
OR
userid, table1.requiredInfo, null
--if table2 does not have an entry

can be easy done with an outher join:

SELECT table1.userid, table1.requiredInfo, table2.optionalInfo
FROM table1 LEFT OUTER JOIN table2 ON table1.userid = table2.userid

[campkev]

whoops, I messed up explaining the situation.
table2 is a many-to-many table with 3 columns, not 2
userid, optionalInfoDesc, OptionalInfoValue

what i need is

if Userid X has an entry in table2 with optionalInfoDesc = Y
return table1.requiredinfo, table2.optionInfoValue

if Userid X does not have an entry in table2 with OptionalInfoDesc = Y
return table1.requiredinfo, null

i tried:
select table1.requiredInfo, table2.optionalInfoValue from table1
left outer join table2 on table1.userid = X and table1.userid = table2.userid and table2.optionalInfoDesc = Y

but that returns a row for every userid, not just userid X

then I tried:
select table1.requiredInfo, table2.optionalInfoValue from table1
inner join table2 on table1.userid = X and table1.userid = table2.userid and table2.optionalInfoDesc = Y

but that only works if table2 has an entry

I tried
select table1.requiredInfo, table2.optionalInfoValue from table1
right outer join table2 on table1.userid = X and table1.userid = table2.userid and table2.optionalInfoDesc = Y

but that returns a row for every entry for userx in table2 not just the row with optionInfo Desc = y

where am I messing up

[vangraan]

Is it possible to post a screen shot of a diagram of the tables you are using?

[campkev]

[IMG=untitled.jpg][/IMG]
examples

storeproc 1 'PreferredName'
returns John, null

storeproc 2 'PreferredName'
returns Joseph, Joe

storeproc 2 'MaidenName'
returns Joseph, null

storeproc 3 'MaidenName'
returns Elizabeth, Smith

[stoefln]

i think it should work like that:
select table1.requiredInfo, table2.optionalInfoValue from table1
left outer join table2 on table1.userid = table2.userid
where table1.userid = X and table2.optionalInfoDesc = Y

[campkev]

nope, that returns one row for every user, not just the user specified. Any I solved the problem by using that select into a temp table and then doing a select from the temp table.

[stoefln]

nope, that returns one row for every user, not just the user specified. Any I solved the problem by using that select into a temp table and then doing a select from the temp table.

"where table1.userid = X " does definatelly NOT return any users which have NOT userid = X

[campkev]

I wouldn't think so either, but that is what is happening.