My SQL Query is creating more records with an INNER Join

3034 views mysql
5

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

answered question

2 Answers

3

just a suggestion

looking to your () sequence in where seems you have two ime the same condition in WHERE

    WHERE  ( qalog.logdate >= Dateadd(year, -3, Getdate()) )
     OR ( qalog.logdate >= Dateadd(year, -3, Getdate()) )

you could avoid

posted this
6

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

posted this

Have an answer?

JD

Please login first before posting an answer.