I'm going to start with some background. You can skip to the bottom if you just want to get to the question:
The previous developer created a project-tracking system where projects start out in the "Forecasted" table and eventually get moved to one of the following tables: Booked, Cancelled, Hold, Lost, or No_Bid. Theoretically, since all projects start out in the "Forecasted" table with a single auto-increment ProjectID, there should never be a duplicated ProjectID among the 6 tables.
However, I have found this to not be the case. The auto-increment value in the Forecasted table must have gotten reset at some point and there are duplicate keys among the six tables. I learned this as I was trying to re-combine the tables.
I don't see the point in the 6 tables anyway since there isn't enough data to warrant splitting it up. The combined size of all tables is 7 MB (that's 7 years worth of data), which can be cut down to half that size through normalization (we're talking '1NF' here).
Question: I'm trying to get a list of all the duplicate project IDs between the 6 tables. The query I have isn't throwing any errors, but it isn't giving me any results either. Could someone point me in the right direction to get the results I want? Here's what I have:
SELECT ProjectID, COUNT(ProjectID) AS pcount
FROM Forecasted_Project
LEFT JOIN Booked_Project USING(ProjectID)
LEFT JOIN Cancelled_Project USING(ProjectID)
LEFT JOIN Hold_Project USING(ProjectID)
LEFT JOIN Lost_Project USING(ProjecID)
LEFT JOIN No_Bid_Project USING(ProjectID)
GROUP BY ProjectID
HAVING COUNT(ProjectID) > 1
