INNER JOIN problem

[jamesm6162]

Hi

I'm Using SQL Server 2005 and have the following query for creating a view:


SELECT c.somefield, p.otherfield, ...and some other fields

FROM TABLE1 AS c INNER JOIN
TABLE2 AS p
ON c.personnel_number = p.personnel_number AND c.period = p.period



Both period and personnel_number appear in both tables and are non unique in the table. But for each period there only exists one of each personnel_number.

After running the query the view ends up with more records than either one of the tables. (180000 compared to 170000)

What can be the cause of this? And what exactly does the AND operator work in that context?

[chazzy]

have you looked to see if it's actually returning you proper results, or are you getting mixmatched results? (ie same personnel number but different period...)

[jamesm6162]

The results all look fine, and when I add DISTINCT the count stays the same.
But theres too many records to actually check them all by inspection.

[r0k3t]

Hmm? I can't seem to recreate the problem, but I don't have any tables that have multiple matching columns, meaning there is just on foreign key.

Couldn't you say something like this.

SELECT c.somefield, p.otherfield, ...and some other fields
FROM TABLE1 AS c INNER JOIN
TABLE2 AS p
ON c.personnel_number = p.personnel_number
WHERE c.period = 1 (or whatever it is...)


because you have joined already on personal_number, then you add a where clause to limit the query. I really think it has something to do with the "AND" in the join... Anyone else have any input?

Thanks