1
  SELECT *
  FROM        GRNHISTORY GR
  inner Join Inventorytransferlog itl on GR.ReferenceEntryID=itl.ReferenceEntryID

Both tables GRNHISTORY and Inventorytransferlog have their own ID column and unique ids. But we did not need it nor are we using it. ReferenceEntryID is a foreign key from another table. Both tables have 10 records. Now when I run this query it shows me 100 records which I seem not to be able to understand. It should be showing 10 records as per my understanding. Please correct me where I am wrong.

6
  • SELECT DISTINCT * FROM...?
    – sibert
    Commented Apr 29, 2018 at 5:10
  • 3
    It may help if you post CREATE TABLE statements including keys and INSERT statements with some sample data. Commented Apr 29, 2018 at 5:26
  • 1
    The 10 records have different / unique value on ReferenceEntryId ? Important here are the values for the ReferenceEntryId field, because you are joining by this field. See here an sample dbfiddle.uk/…
    – Sabin B
    Commented Apr 29, 2018 at 5:26
  • 5
    As 10 * 10 = 100, I suspect your query is just producing a Cartesian product. Do all rows in both tables have the same value in ReferenceEntryID?
    – sticky bit
    Commented Apr 29, 2018 at 6:20
  • 1
    SELECT DISTINCT is very unlikely to help, in this case. Commented Apr 29, 2018 at 10:53

2 Answers 2

6

The answer becomes obvious when valid values are inserted for the PRIMARY KEYs of the two tables you are joining and you look at the results:

An example:

The two tables:

CREATE TABLE fred (int_1 INTEGER, ref1 INTEGER);   -- equivalent of GRNHISTORY
CREATE TABLE bill (int_2 INTEGER, ref2 INTEGER);   -- of Inventorytransferlog

The data:

INSERT INTO fred VALUES (1, 55), (2, 56), (3, 57);
INSERT INTO bill VALUES (21, 55), (22, 56), (23, 57); 
       -- NOTE: only 1 matching value of the JOINing refrerence field per table!
       -- and NOTE: different PRIMARY KEYs for the two tables! 

The (your) SQL:

SELECT * FROM fred f
INNER JOIN bill b ON f.ref1 = b.ref2

The result (and fiddle):

int1 ref1 int2 ref2
   1   55   21   55
   2   56   22   56
   3   57   23   57

But, if the data is now changed as follows:

INSERT INTO fred VALUES (1, 55), (2, 55), (3, 55);
INSERT INTO bill VALUES (21, 55), (22, 55), (23, 55);  

       -- NOTE: 55 - ALL the values of JOINing reference columns in each record match!

Note that the joining field now has the same value in both tables for all records, then run the same SQL as above. Result (and fiddle):

  int1  ref1 int1 ref2
    1   55  21  55
    2   55  21  55
    3   55  21  55
    1   55  22  55
    2   55  22  55
    3   55  22  55
    1   55  23  55
    2   55  23  55
    3   55  23  55

And the result is a 9 (i.e. 3 x 3) records (effective) CROSS JOIN because your data makes it so! Because you've asked the SQL to JOIN on very common values in BOTH tables so therefore you can expect a lot of records in the result!

You can check this out by doing a

SELECT COUNT(DISTINCT(ref1)) AS distinct_ref FROM fred;

answer = 1, (and the same result for bill with ref2).

I'm willing to bet that in your tables where you are getting a CROSS JOIN (or something very similar), that the SQL above using the ReferenceEntryID will get you very low counts (which also match in both tables) leading to many matching records in your SELECT.

There's the explanation for your apparent multiplication or CROSS JOIN - you have too many matching records in both tables - et voilà!

1

I suspect the problem is that you are not using the unique ID you decided you don't need. Foreign keys are supposed to point to keys.

I suspect both tables have 10 rows with the same ReferenceEntryID.

SELECT *
FROM GRNHISTORY GR
Join Inventorytransferlog itl 
  on GR.ReferenceEntryID = itl.ReferenceEntryID

I suspect you need something like GR.ID = itl.ReferenceEntryID.

4
  • Foreign keys are supposed to point to keys. - maybe, maybe not. If you're joining through a joining table (Associative Entity), you mightn't be JOINing to a unique PRIMARY KEY - say if you're JOINing from a crew table to a crew_flight table to an airport table. Or from a student through a student_course table to a course_name field?
    – Vérace
    Commented Apr 29, 2018 at 22:04
  • @Vérace Are you suspecting GRNHISTORY or Inventorytransferlog to be join table. If there was a join table I would expect 3 tables. Even on a join table it is pointing to a composite key.
    – paparazzo
    Commented Apr 29, 2018 at 22:08
  • True enough - I was just pointing out that your assertion that Foreign keys are supposed to point to keys is not universally true - although I will give you that if there are only two tables, that's what one would expect! However, one part of a composite key is not a PRIMARY KEY.
    – Vérace
    Commented Apr 29, 2018 at 22:10
  • 2
    @Vérace Cool. Did not want to confuse the OP.
    – paparazzo
    Commented Apr 29, 2018 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.