joins

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 also to exist in table
Team
(I used the keyword in plus a subquery but it is very slow)
can u help?
Thanx in advanceP wrote on Thu, 9 Mar 2006 15:22:04 +0200:

> 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 also to exist in
> table Team
> (I used the keyword in plus a subquery but it is very slow)
> can u help?
> Thanx in advance
How about
SELECT a.*
FROM Alpha a
INNER JOIN Team t1 on a.team1 = t1.teamid
INNER JOIN Team t2 on a.team2 = t2.teamid
Dan|||THANX A LOT!!!
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:udwRIB4QGHA.5296@.tk2msftngp13.phx.gbl...
>P wrote on Thu, 9 Mar 2006 15:22:04 +0200:
>
> How about
> SELECT a.*
> FROM Alpha a
> INNER JOIN Team t1 on a.team1 = t1.teamid
> INNER JOIN Team t2 on a.team2 = t2.teamid
>
> Dan
>

Join Returns too many rows

Hi

I'm sure this is a real noob question and it may be something I have know the answer to in the past but I can't remember and its been driving me mad for hours. If anyone can tell me how to do this it would make my day!

I have simplified the problem for the purpose of clarity and have attached a sript to create a simple example table.

the table looks like this:

id cMatch cData
1 A A1
2 B B1
3 C C1
4 B B2
5 A A2
6 B B3

I want to be able to do a join on the two table that only returns the following:

t1.cData t2.cData
A1 A2
B1 B2
B1 B3

The Closest I can get is with the following qry:

SELECT t1.cdata, t2.cdata from tmp_Table1 t1
JOIN tmp_Table1 t2 ON t1.cMatch=t2.cMatch AND t1.cdata<>t2.cdata
WHERE t1.cdata<t2.cdata
ORDER BY t1.cdata, t2.cdata

Which returns:

t1.cData t2.cData
A1 A2
B1 B2
B1 B3
B2 B3Not sure if I attached the script last time so I thought I'd make sure.

thanks in advance for all your help!

Andy|||Hi

while not knowing your specific database, this will work with the example you provided:

SELECT MIN(cdata1), cdata2 FROM
(
SELECT t1.cdata AS cdata1, t2.cdata AS cdata2 FROM tmp_Table1 t1
INNER JOIN tmp_Table1 t2 ON t1.cMatch=t2.cMatch
AND t1.cData <> t2.cData
AND t1.id < t2.id)
AS subtable
GROUP BY cdata2

you may have to change the aggragation function that evaluates the correct value to choose.