Select statement using 2 tables returns 4million reocrds instead of 500k

Question

I created a small mapping table to associate values with values from another table. When I perform the select I get back 4million reocrds~.

I know it has to be something trivial I'm doing. Would appreciate if someone could A) Tell me what I'm doing wrong and B) Tell me what literature I should be reading so I understand these issues :) (Websites, books etc.)

select u.uid, 
       l.catid 
  from usersmap u, 
       locmapping l 
 where u.prodid = l.prodid

Sorry as for the extra data I'm getting back I'm assuming its duplicated in some form. What the query is attempting to do is get a user id and product id. I'm trying to replace product id with a category id which is coming from the other table.

The database structure is as follows

locmapping (Table)

• id (int)
• catid (int)
• prodid (varchar25))

usermap (Table)

• id (int)
• prodid (varchar)
• uid (varchar)

The old situation I had was just to select prodid, uid from usermap

But now I'd like to make prodid equal to catid. I use the locmapping to create these mappings (In theory :) )

Answer 1

The SQL is semantically correct. If you're getting more records back than you expect it's most likely because you need to add a second pair of columns to WHERE clause to further limit how records are "matched" between the two tables, or limit the records considered from one of the tables to a subset (perhaps the current or most recent record found).

Any further guess as to what's wrong is not possible without a more complete description of the problem, your table structure and ideally some sample data.

Answer 2

Go to SQL School: http://www.w3schools.com/sql/default.asp

Without knowing how many rows are you the respective tables, it's hard to know if you're doing something wrong or not. i.e. if you have 4 million records in table A, and they're also all in table B (according to the field that you're joining on), then you're going to get 4 million records!

Also, this is merely a SQL issue, and has nothing to do with websites.

Answer 3

Without a data model it's difficult to tell where the problem is. Perhaps you are misunderstanding how an inner join works?

Your query is using an inner join. This means there must be 4 million intersections. Usersmap could have 500,000 rows but if the prodid of one of those rows is contained in multiple rows in locmapping then you will get a row for each intersection.

Answer 4

From these table structures it looks as though there is a many-to-many relationship between categories and products, and also between users and products.

If so, it would be possible for one user to map to one category via many products, which is why there are so many additional rows - in which case, the answer is simply to add DISTINCT to the query, like so:

select DISTINCT u.uid, l.catid 
from usersmap u, locmapping l where u.prodid=l.prodid

Answer 5

If SELECT prodid, uid FROM usermap produces 500K rows, but the SQL in your question produces 4 million rows, it means that each value of prodid is found (on average) in 8 rows of the table locmapping. That is causing the original 500K rows to be multiplied by 8 and those rows are coming back with a variety of catid values.

If locmapping is intended to provide a single catid per prodid, you should protect the prodid column in that table with a UNIQUE index.