Joining to questions to one

[ribberbomb]

Hi!
I've got a problem with a SQL-statment. A have created two separate questions A and B in MS Access. If I use the designview I easly pick up the two questions and link them together with the common attribute GameNr and I get the result I want. The thing is that I want to use to use the SQL in a asp-page with variables so I must have it in clean SQL.

OK, just then put the two statments together, but I how??? Here is the two questions A and B, keys and the functional SQL statment which I want with fully outwritten SQL.

Question saved as A
SELECT T.GameNr, T.type, TR.rank, TR.level, TR.GamePlayed, TR.money
FROM T, TR
WHERE T.GameNr=TR.GameNr AND TR.alias="player";

Question saved as B
SELECT TR.GameNr
FROM Players, TR, Persons
WHERE Players.alias=Persons.alias And Players.alias=TR.alias AND Persons.group="G";

Keys:
Persons: login
Players: alias
TR: alias (fk from Players), GameNr (fk from T), rank
T: GameNr

Working SQL from designview:
SELECT A.GameNr, A.Turntyp, A.Rank, A.Level, A.GamePlayed, A.Money
FROM A, B
WHERE B.GameNr = A.GameNr;

Or, the same with JOIN:
SELECT A.GameNr, A.Turntyp, A.Rank, A.Level, A.GamePlayed, A.Money
FROM B INNER JOIN A ON B.GameNr = A.GameNr;

Insead of the two above statments I want one with all the SQL code from A and B in it so I can use it in a asp-page where TR.alias and Persons.group changes.

I thank in advance for any help on this issue.
Regards,
Johan Ribbeklint

 

[tlbroadbent]

You can Join queries (derived tables) as you do tables.

SELECT
A.GameNr, A.Turntyp, A.Rank,
A.Level, A.GamePlayed, A.Money
FROM
(SELECT
T.GameNr, T.type, TR.rank,
TR.level, TR.GamePlayed, TR.money
FROM T
Inner Join TR
ON T.GameNr=TR.GameNr
WHERE TR.alias='player') As A

INNER JOIN

(SELECT TR.GameNr
FROM Players
Inner Join TR On Players.alias=TR.alias
Inner Join Persons On Players.alias=Persons.alias
WHERE Persons.group='G') As B

ON A.GameNr = B.GameNr

------------------

Unless I'm missing something in your explanation, I believe the following query will return the same result. It is much simpler.

SELECT
T.GameNr, T.type, TR.rank,
TR.level, TR.GamePlayed, TR.money
FROM T
INNER JOIN TR
ON T.GameNr=TR.GameNr
INNER JOIN Persons
ON TR.alias=Persons.alias
WHERE TR.alias='player'
AND Persons.group='G' Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ribberbomb]

Thanks Terry for the help. It works great!
Regards,
Johan Ribbeklint