StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Query returns duplicated results
text
Body
copied!<p>I am fairly new at MySQL and I am working with a database system which has four main tables, described here:</p> <p><a href="http://www.pastie.org/3832181" rel="nofollow">http://www.pastie.org/3832181</a></p> <p>The table that this query primarily works with is here:</p> <p><a href="http://www.pastie.org/3832184" rel="nofollow">http://www.pastie.org/3832184</a></p> <p>Seems fairly simple right?</p> <p>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.</p> <p>This is my query to perform that action.</p> <pre><code>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; </code></pre> <p>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.</p> <p>Thanks, if you can help me figure this out.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload