query

[platp]

hello,
I have a noob question

I have a table called Alpha

aplha_id team1 team2

and a table called Team

teamid teamname

I cannot get the correct query in order to retrieve all the data from table
Alpha where team1 to exist in table Team and team2 to exist in table Team OR TO be 0

(I used the keyword in plus a subquery but it is very slow)
can u help?
Thanx in advance!!

 

[SQLBill]

Can you give us some sample data?

This MIGHT work, but it depends on what you are really looking for and what your data really looks like.

SELECT A.*
FROM Alpha A
 LEFT OUTER JOIN Team T
  ON A.Alpha_ID = T.TeamID
WHERE T.TeamName = 'team1'
  AND (T.TeamName = 'team2'
   OR (T.TeamName = 0)

-SQLBill

Posting advice: FAQ481-4875

 

[platp]

Sorry I was not clear
TABLE ALPHA
aplha_id team1 team2
-------- ------ -----
1 100 106
2 109 111
3 100 0

TABLE TEAM
teamid teamname
------ --------
100 ars
106 mtc
109 juv
200 aja

I want a query that retrieves only the first and the third record in the above example (because 11 does not exist in TEAM)

 

[SQLBill]

Ouch....there's no common field to JOIN the tables. Maybe this will work......

SELECT A.*
 FROM Alpha A,
      Team T
 WHERE A.Team1 = T.TeamID
   AND (A.Team2 = T.TeamId
    OR A.Team2 = 0)

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

One way:

select A.*
from alpha A
inner join team T1 on A.team1 = T1.teamid
left outer join team T2 on A.team2 = T2.teamid
where (T2.teamid is not null or A.team2 = 0)

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]