How to use a value from one recordset in another..?

[Bane]

Im doing a select that should retrieve a name from one table and display the
number of correct bets done in the betDB (using the gameDB that has info on
how a game ended)

I want the "MyVAR" value to be used in the inner select statement without
too much hassle. As you can see im trying to get the "MyVAR" to insert in
the bottom line of the code.

Whats the quick fix to this one..?

Thanks in advance :-)

---------- code begin ----------
select memberDB.memberID as MyVAR, (select count(GamesDB.GameID)
from GamesDB
inner join GameBetDB
on GameBetDB.betHome = GamesDB.homeGoal and GameBetDB.betAway =
GamesDB.awaygoal
inner join memberDB
on memberDB.memberID = GameBetDB.memberID
where GamesDB.gameID=GameBetDB.gameID
and GameBetDB.memberID= MyVAR ) as wins from memberDB
---------- code end ----------

[Erland Sommarskog]

Bane (ba**@noname.net) writes:

Im doing a select that should retrieve a name from one table and display
the number of correct bets done in the betDB (using the gameDB that has
info on how a game ended)

I want the "MyVAR" value to be used in the inner select statement without
too much hassle. As you can see im trying to get the "MyVAR" to insert in
the bottom line of the code.

Whats the quick fix to this one..?

Thanks in advance :-)

---------- code begin ----------
select memberDB.memberID as MyVAR, (select count(GamesDB.GameID)
from GamesDB
inner join GameBetDB
on GameBetDB.betHome = GamesDB.homeGoal and GameBetDB.betAway =
GamesDB.awaygoal
inner join memberDB
on memberDB.memberID = GameBetDB.memberID
where GamesDB.gameID=GameBetDB.gameID
and GameBetDB.memberID= MyVAR ) as wins from memberDB
---------- code end ----------

Well, the answer to the question as posted is: use aliases, like this:

select m.memberID as MyVAR,
(select count(g.GameID)
from GamesDB g
join GameBetDB gb on gb.betHome = g.homeGoal
and gb.betAway = g.awaygoal
join memberDB m2 on m2.memberID = gb.memberID
where g.gameID=gb.gameID
and gb.memberID = m.memberID) as wins
from memberDB m

But that inner memberDB does not make any sense to me. I think you
are better off with:

select m.memberID as MyVAR,
(select count(g.GameID)
from GamesDB g
join GameBetDB gb on gb.betHome = g.homeGoal
and gb.betAway = g.awaygoal
where g.gameID=gb.gameID
and gb.memberID = m.memberID) as wins
from memberDB m
Then of course the join conditions between GamesDB and GameBetDB looks
funny. Surely g.gameID = gb.gameID is the join condition? The other two
looks more like filter to me. (This is a theoretical issue only, though,
and does not affect the result.)

And finally I don't see the need for the nested subquery. Maybe it is
as simple as?
select m.memberID, count(g.GameID)
from GamesDB g
join GameBetDB gb on g.gameID=gb.gameID
and gb.betHome = g.homeGoal
and gb.betAway = g.awaygoal
join memberDB m on m.memberID = gb.memberID
group by m.memberID

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp