Solved

Query for non-duplicates in same table.

Posted on 2011-09-24
3
250 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:Ron1959
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 125 total points
ID: 36593389
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
 
LVL 52

Expert Comment

by:_agx_
ID: 36593402
>> 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
 

Author Closing Comment

by:Ron1959
ID: 36593691
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question