Select posts where the ID is not within all of the posts 1

[Sonrie]

Hello,

I am working on a query for an Access Database. It’s a database about a competition where the results for the round are registered at the end of each round.

I want to select data about the persons in the register who have never competed or not competed in one or more of the rounds.

The tables are:
-Contestants: there are registered contestants with the primary key ContID, and attributes Firstname and Lastname.
-Roundresults: there are registered round results for each round that is finished with the primary key RoundResID, the foreign key ContID and Round, and the attribute result.
-Round: there are registered 5 rounds, with the primary key Round.

Can someone please help me figure out how to create the query?

Sincerely,
Sonrie

 

[BarkingFrog]

SELECT a.Firstname, a.Lastname
FROM Contestants a, Roundresults b
WHERE a.ContID <> b.ContID

This will return the first and last names of everyone who is not listed in the Roundresults table.

 

[Sonrie]

I tried that, but it retuned all of the names X times. When I put DISCINCT after SELECT it returned all the names of the contestants - but only once. Why doesn't your tip work?

I want to list out the data about the contestants that have never competed, or not competed in one or more of the rounds – i.e. the ContID might be in one of the round results, but not in all.

There are rounds where no results are registered, but I will ignore those.

Sincerely,
Sonrie

 

[r937]

>> &quot;I want to list out the data about the contestants
>> that have never competed, or not competed in one or
>> more of the rounds – i.e. the ContID might be in one
>> of the round results, but not in all&quot;

the &quot;never competed&quot; part is a left outer join where there is no match

the other requirement is still vague

if there are 5 rounds, &quot;not competed in one or more of the rounds&quot; might mean &quot;any number less than 5&quot; or &quot;not in a specific round&quot;

could you clarify please?


rudy

 

[Sonrie]

In the table Round there are registered 5 rounds (total rounds for the competition). But in Roundresults there are only registered the results for the 3 first rounds (the rest will come later…).

I want to select the contestants that are:
1) not registered in any of these rounds
2) and, is registered in one or more, AND left out in one or more of the rounds.

I have tried this query to count the number of rounds where there are registered results, and it returns 3 (correct):
SELECT COUNT(Etappe)
FROM (SELECT DISTINCT Etappe FROM Etapperesultat);

 

[r937]

try this --

[tt]select C.ContID
, Firstname
, Lastname
from Contestants C
left outer
join Roundresults RR
on C.ContID = RR.ContID
group
by C.ContID
, Firstname
, Lastname
having count(RR.ContID) < number[/tt]

rudy

 

[Sonrie]

Sorry, wrong query:
SELECT COUNT(Round)
FROM (SELECT DISTINCT Round FROM Roundresults);

 

[Sonrie]

Thank you! It worked!

Sincerely,
Sonrie