Inner Join returns duplicate entries?

[Isuldor]

I'm using a pretty simple inner join on two tables but the some of the same results are showing up twice. If I just remove the inner join all the results are uniquely present. For some reason the inner join creates about 10% more records that are all duplicate entries. Any idea what might cause this?

SELECT products.product_name
FROM products INNER JOIN categories ON products.category = categories.category_name
WHERE ((products.X)=True);

 

[The_Doc_Man]

If the joined tables are one/many AND you happen to not retrieve any of the fields from the many-side that would show you that the records are different (i.e. PK of many-side table), it would APPEAR that you have duplicates.

When you DON'T do the join, the table named first in the query "drives" the result. When you DO include the join, the (virtual) JOINED table drives the result. In the former case, if all first-table (left-hand-side table) entries are unique, you get unique entries. If the JOIN is in play, you get all entries formed by the JOIN.

 

[Moniker]

What Doc said is spot on, and a quickie solution is to use SELECT DISTINCT instead of SELECT. In non-technical terms, if your record exists on both sides and your many side has more than one entry, then it will appear to duplicate. So, if your product is in more than one category (a CD might be in both the optical media and removable media categories, for example), then it will show up twice (or more times).