Below is the query I have been using until I was asked to join another table below called Disiposition.
DispositionID Disposition 1 All Good 2 Used with problems 3 Rework 4 Scrapped 5 Return to Vendor
The Rejections table (already linked to this query also has a "DispositionID" column. All I was trying to do is join the table above to show which of the 5 dispostions was applied to each inspection. Every time I try something, the total number of records found go from 6,841 to 34,205. So it looks like it is assigning one of each of the above dispositions to the original 6,841 records, when I know that there is only on that has been assigned when the record was added to the database. Any help would be greatly.
SELECT qalog.qalid, qalog.groupid, qalog.logdate, qalog.sonumber, qalog.partnumber, qalog.partnotes, qalog.travelerqty, qalog.euser, qalog.itid, qalog.trackingnumber, qalog.mdr, qalog.expirationdate, qalog.ponumber, qalog.receiptno, qalog.reasonid, qalog.rrno, rejections.numdiscrp, rejections.rrid, rejectreason.reason, productgroups.groupname, disposition.disposition FROM qalog INNER JOIN rejections ON qalog.qalid = rejections.qalid INNER JOIN rejectreason ON rejectreason.rrid = rejections.rrid INNER JOIN disposition ON rejections.dispositionid = rejections.dispositionid INNER JOIN productgroups ON productgroups.pgid = qalog.groupid WHERE ( qalog.logdate >= Dateadd(year, -3, Getdate()) ) AND ( qalog.itid = '3' ) AND ( rejectreason.groupid = '0' ) OR ( qalog.logdate >= Dateadd(year, -3, Getdate()) ) AND ( qalog.itid = '3' ) AND ( rejectreason.groupid = '3' ) ORDER BY qalog.qalid
Take a really close look at your join condition:
INNER JOIN disposition ON rejections.dispositionid = rejections.dispositionid
I’ve reformatted your code hopefully to make it easier to see
After you correct the problem, if you’re still getting more rows out than you intended it’s and indication that there are multiple rows in the table you’re joining that match to rows in the tables you already have in the query. This indicates you need to add more conditions so that the row matching is 1:1
For example if there is a date indicating when a disposition was valid from/to and it’s a business rule that the current disposition has a null date you join on ID and also
datexyz is null
As to why your original form (the mistake in the join) caused rows to multiply, as a predicate it is always true, realising what we call a Cartesian product. The number of rows you end up with is the number of rows on the left multiplied by the number of rows on the right. Every row ends up crossed with every other row. It can be a useful technique sometimes, though it’s not in this case. As has been commented elsewhere, if you unexpectedly get duplicates while coding you should focus on working out where they crept in rather than using distinct or group to remove them again. In nearly all cases they can be prevented at source rather than quashed later and it’s always preferred as it makes your database’s life easier