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.

Please stop using the [old JOIN syntax](http://stackoverflow.com/questions/894490/). — DCoder, Apr 22 '12 at 07:04

score 2 · Answer 1 · answered Apr 22 '12 at 06:51

2

Use distinct keyword to remove duplicates.

Have a look here

answered Apr 22 '12 at 06:51

Shashank Kadne

7,993
6
41
54

That definitely works. I was curious if they may be an error in my query which would correct the problem. – sbadams Apr 22 '12 at 06:54

score 1 · Answer 2 · answered Apr 22 '12 at 07:00

You might be missing a constraint somewhere when you are doing the join in your SQL statement. From what I got out of your description, it sounds like this query might work for you.

SELECT 
    User.UserID,
    Business.BusinessID,
    Business.BusinessName
FROM
    User,
    UserBusinessOpportunity,
    Business
WHERE
    User.UserID = 1 AND
    User.UserID = UserBusinessOpportunity.UserID AND
    UserBusinessOpportunity.OpportunityID IS NULL AND
    UserBusinessOpportunity.BusinessID = Business.BusinessID

Julian · Answer 3 · 2012-04-22T07:11:25.797

1

I think you intend an inner join but your query is an implicit outer join.

Joining with null fields often works best with a left join.

Try this:

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

Julian

edited Apr 22 '12 at 07:11

answered Apr 22 '12 at 07:10

Julian

1,522
11
26

By the way, you should get some kind of award for this question. Its a perfect model of how to provide exactly the information needed to get help. If you had provided the sql for the schema - "SHOW CREATE TABLE" instead of "DESCRIBE" I could even have loaded it and run the queries. – Julian Apr 22 '12 at 09:46

MySQL Query returns duplicated results

3 Answers3