SQL JOIN limit results to rows where specific value does not exist

I am joining two tables using SQL. I'm joining a table which contains charter flight information and a table which contains the crew assigned. In my results, I only want to display the rows that only have a value of "Pilot" in the the crew table and not "Copilot" or both.

SELECT * FROM TABLE_A JOIN TABLE_B ON (TABLE_A.Value = TABLE_B.Value) WHERE TABLE_A.OtherValue = 'Pilot'

This is off the top of my head, so some syntax may be off. The main point is the WHERE clause. You can specify the value that you are looking for in the column (in your case you are looking for Pilot).

EDIT: To prevent a value you can do something like WHERE TABLE.VALUE != 'Copilot' != may need to be written as <> depending on the what SQL it is.

EDIT2: My SQL-Server is throwing a hissy and not connecting, so this is also entirely off the top of my head and I think it's a bit of a hack-job, but I think it'll do the job. :)
SELECT [CHARTER].*, COUNT(*) as Tally FROM [CHARTER] JOIN [CREW] ON ([CHARTER].[CHAR_TRIP] = [CREW].[CHAR_TRIP]) WHERE [CREW].[CREW_JOB] = 'PILOT' OR [CREW].[CREW_JOB] = 'COPILOT' GROUP BY [CHARTER].* HAVING Tally = 1
This assume that all flights have a pilot, but not all flights have a co-pilot. To get the exact display you want, you might have to use it as a sub-query (to remove the Tally column).

SELECT *
FROM charter ch
JOIN crew cr ON ch.char_trip = cr.char_trip
WHERE NOT EXISTS(SELECT *
                   FROM crew cr2
                  WHERE cr2.char_trip = ch.char_trip
                    AND cr2.crew_job != 'PILOT')

I think that should do the trick. The join to the crew table in line 3 is optional, and only if you need results from that table. The NOT EXISTS anti-join is what evaluates all crew for a given trip and checks for any that are not pilots.

You should really help us out here with the schema for us to provide you with a decent query. I think the most important thing here is how do you determine who is a pilot and/or copilot and how do you relate each person to the flight.

I think something like this might help:

SELECT * FROM Charter C
INNER JOIN Crew ON (Charter.CHAR_TRIP = Crew.CHAR_TRIP)
WHERE Crew.Crew_Job = 'PILOT' AND (SELECT COUNT(*) FROM Charter
                                   INNER JOIN Crew ON (Charter.CHAR_TRIP = Crew.CHAR_TRIP)
                                   WHERE Crew.Crew_Job = 'CoPilot' 
                                         AND Charter.Chart_Trip = C.ChartTrip) = 0

Although this might not be the cleanest solution.. it should do the work.