0

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

3
Contributors
8
Replies
9
Views
11 Years
Discussion Span
Last Post by campkev
0

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

0

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

0

[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

0

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

0

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.

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.