MySQL Query returns duplicated results

Question

I am fairly new at MySQL and I am working with a database system which has four main tables, described here:

http://www.pastie.org/3832181

The table that this query primarily works with is here:

http://www.pastie.org/3832184

Seems fairly simple right?

My query's purpose is to grab all the BusinessID's for a explicit User where the OpportunityID's are NULL, once it has those BusinessID's, I want it to find the associated BusinessName in the Business table and match that BusinessName with the BusinessName(Business) in the EmploymentOpportunity table.

This is my query to perform that action.

SELECT EmploymentOpportunity.OpportunityID, Business, Description 
FROM UserBusinessOpportunity, Business, EmploymentOpportunity
WHERE UserBusinessOpportunity.BusinessID = 
          (SELECT UserBusinessOpportunity.BusinessID 
          FROM UserBusinessOpportunity 
          WHERE UserBusinessOpportunity.UserID=1 AND
                UserBusinessOpportunity.OpportunityID is NULL) 
       AND UserBusinessOpportunity.BusinessID = Business.BusinessID 
       AND Business.BusinessName = EmploymentOpportunity.Business;

The sub-select statement is supposed to return the subset of BusinessID's. I'm sure this is an extremely simple query, but it keeps giving me duplicate results and I'm sure why. The set of results should be 3, but it's sending me 24, or 8 repeating sets of those 3.

Thanks, if you can help me figure this out.

Answer 1

Use distinct keyword to remove duplicates.

Have a look here