Outer Join 1

[WarcraftPlayer]

Ok I have reached the end of my rope. Ive tried inner and outer joins and cant find the right combonation.

I have 2 tables

[b]USERS[/b]
UserID    FNAME     LNAME    PWD     EMAIL
AJC       Alan      Cohn     AJC     ACohn@whereever.com
BJJ       Brian     Jones    BJJ     BJones@whereever.com
CCC       Corina    Connie   CCC     CConnie@whereever.com
DDD       Dawn      Dower    DDD     DDower@whereever.com
KLS       Kelly     Smith    KLS     KSmith@whereever.com

[b]Teams[/b]
TeamID    TeamInitials TeamName         TeamLeadID
1         NIT          NightTrain       AJC
2         DRT          DreamTeam        KLS
3         MDM          Midday Madness   CCC

If I want to get the email address for a certain user, thats easy enough. The thing is if any of the team initials are put in I want the email address for the TeamLeadID.

So say NIT is put in the where clause I want to get the email for AJC.

Thanks in advance for any help

 

[grega]

select email
from teams, users
where team.teaminitials = "NIT"
and   team.teamleadid = users.userid

should do it.

Greg.

 

[grega]

BTW, that's just a normal join.

Greg.

 

[nagornyi]

It is clear from the question that you are not satisfied with the inner join like

select
  t.teaminitials
, u.email
from 
  users u
, teams t
where
  u.userid = t.teamleaderid

...but not clear why?
For all teaminitials the SQL returns leader's email...correct?

 

[WarcraftPlayer]

Thanks for you help but not quite what I need, I may not have explaned it clearly.

If the initials DDD are passed in, I want the email DDower@whereever.com

or if AJC is passed in, I want the email ACohn@whereever.com

but if say NIT is passed in, I want the email ACohn@whereever.com

The example you gave me will give me a match with team initials. But does nothing if they are in the users table.

So basically I need to check both tables not knowing exactly which table the initals may get a result. If they match a userid give me that email address but if they match teaminitals then give me the email address of the the team lead.

None of the teams initals are in the users table.

It really does not matter to me how I get the result. Inner or Outer or none is fine with me.

Thanks for both your help so far.

 

[nagornyi]

(
select
  userid id
, email
from
users
)
[b]union[/b]
(
select
  t.teaminitials
, u.email
from
  users u
, teams t
where
  u.userid = t.teamleaderid
)

 

[WarcraftPlayer]

nagornyi, with that query I get everything. Where would I put in the initials to compare it to the userid and teamleaderid?

 

[nagornyi]

[b]select email from[/b]
{
(
select
  userid id
, email
from
users
)
union
(
select
  t.teaminitials
, u.email
from
  users u
, teams t
where
  u.userid = t.teamleaderid
)
)
[b]where id = 'NIT'[/b]

 

[WarcraftPlayer]

I managed to get it together thanks nagornyi

select userid,email from users
where userid = 'NIT'
union
select userid,email from 
Users u left Join teams t on u.userid = t.teamleadid
where teaminitials = 'NIT'