Unique Joins

I was wrapping up the analysis on a problem the customer had reported. I needed to do one last check for a certain set of records. So I joined two tables. I tried to exclude records that had duplicates in one of the tables. That should be easy, right? I did a GROUP BY, and ensured HAVING(COUNT(*) = 1).

Turns out I kept getting false positives in my query results. I could not explain it. I tried rewriting the query using an inner query. This still resulted in false positives. This was really annoying. It was the last query I needed to perform to wrap up my high priority analysis.

I told one of the leads that I was having trouble with this final SQL.He told me he could assist. I said come on and help me figure this out. We joined a conference call. The lead informed us that he was a SQL guru. I explained what I was trying to do. He rewrote the query too. But there was one problem. He could not get his query to compile.

Around an hour later, I told everyone I needed to go. My lead was still trying to get permutations of the SQL to compile. We were getting nowhere. I would have appreciated a little help here. I was not going to get it. In the end, the lead assigned the task to the database lead, who figured it out in no time.

What is the moral of the story? Beware of self professed SQL gurus.