query show who dosent has medal3

[mokhecen]

i want to make a query show me only who dosent has medal3
the table has tow fields Fname and Medalname
Fname Medalname


rony medal1
rony medal3
sam medal1
john medal3
john medal2
tony medal1


the answer must be only "sam and tony"

[pbaldy]

Presuming you have a table of the people, I see a two step solution. Create a query that returns all the people who do have medal3, then use the unmatched query wizard to compare that query to the table of people, which will give you everybody who does not have medal3.

[jameslarsen]

Try using the NOT IN operator:

SELECT Fname
FROM MyTable
WHERE Fname NOT IN
(
SELECT Fname
FROM MyTable
WHERE Medalname="medal3"
)

If the NOT IN query is too slow (uses more resources to calculate), you can also use a LEFT JOIN query:

SELECT T1.Fname
FROM MyTable AS T1 LEFT JOIN
(
SELECT Fname
FROM MyTable
WHERE Medalname = "medal3"
) AS T2 ON T1.Fname = T2.Fname
WHERE T2.Fname Is Null

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com