Query for non-duplicates in same table.

We have one table which stores witness forms for numerous legislative bills.  The same witnesses may fill out forms for multiple bills over a period of time.

We need a query that will find all the witnesses  who have filled out forms for a past bill and NOT yet filled out a form for a specified new bill.

Would it be best to create a temporary table from the first criteria and then use NOT EXISTS?  Would it be best to simply use GROUP BY?
Can JOIN be used which only one table?

We can assume that Last_Name, First_Name are accurate unique identifiers for witnesses.

Fields would include:

First_Name
Last_Name
DATE
Bill_Number

Data for Bill_Number would look like "SB 1", "SB 2", "HB5" etc.

Here's a non-functioning schematic of what I want to do:

SELECT First_Name, Last_Name, Bill_Number FROM `libertytools`.`testimony`
WHERE `Bill_Number` = "SB 2"

NOT EXISTS

SELECT First_Name, Last_Name, Bill_Number FROM `libertytools`.`testimony`
WHERE `Bill_Number` = "SB 8";

Thanks!
Ron1959Asked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
IF the names really were unique, a not exists with distinct would work

SELECT  DISTINCT p.First_Name, p.Last_Name
FROM     `libertytools`.`testimony` p
WHERE  p.Bill_Number = "SB 2"
AND      NOT EXISTS (
            SELECT 1
            FROM  `libertytools`.`testimony` n
            WHERE n.First_Name = p.First_Name
            AND      n.Last_Name = p.Last_Name
            AND     n.Bill_Number = "SB 8"
 )
0
 
_agx_Commented:
>> We have one table which stores witness forms for numerous legislative bills.  
>> The same witnesses may fill out forms for multiple bills over a period of time.

If you have control over the structure, it'd be better to store the unique witnesses and legislative bills in separate tables. Then store the relationships (ie witnesses for each bills) in a 3rd table

Witness:   WitnessID (Unique PK), FirstName, LastName, ....
LegislativeBill:   LegistlativeBillID (Unique PK), Bill_Number, ...
LegislativeBillWitnesses:  WitnessID , LegistlativeBillID, .....
0
 
Ron1959Author Commented:
Works perfectly! Just what I was looking for.

If the witness enters their name differently from time to time, we will double up on them, but for this purpose it doesn't have to be perfect.

We would use two or three tables to store this data instead of one if the users were from a fairly static group, but witnesses will not be consistent.

Thank you!

- Ron
0
All Courses

From novice to tech pro — start learning today.