CODEWITHSUNDEEP
sqlms-access
Kittoes0124
Kittoes0124

Reputation: 5080

SQL Inner Join giving wrong results

I know that the answer to this is going to be extremely simple and I've solved this problem before but for some reason I've been stuck on this for an hour.

I have the following query:

SELECT table1.FIELD1, table2.sum(FIELD2), table3.sum(FIELD3)
FROM (table1 INNER JOIN table2 ON table1.JOBINSTID = table2.JOBINSTID)
     INNER JOIN table3 ON table1.JOBINSTID = table3.JOBINSTID
GROUP BY FIELD1;

The results that it returns are totally bonkers. Instead of giving me the actual sum of Field2 it gives me a number WAYYY higher. I know why this is happening and somewhat understand it but I don't know how to fix it. How do I change this query so that I get the correct sum?

EDIT: JOBINSTID is a unique ID in tables 1 and 2 but not in table 3.

Upvotes: 1

Views: 1688

Answers (3)

Steve Kass
Steve Kass

Reputation: 7184

If it's only a many-to-one problem involving table1, you might want this:

SELECT
  table2.FIELD1,
  SUM(table3.FIELD2)
FROM table2 JOIN table3
ON table3.JOBINST_ID = table2.JOBINSTID
WHERE EXISTS (
  SELECT * FROM table1
  WHERE table1.JOBINSTID = table2.JOBINSTID
  -- AND table1.JOBINSTID = table3.JOBINST_ID -- redundant, but might improve runtime
)
GROUP BY table2.FIELD1;

If the problem is (also) between table2 and table3, you may need to rethink how your database is normalized, but you could try this, which I think will work in any case:

SELECT -- DISTINCT -- add DISTINCT if FIELD1 values are not unique in table2
  table2.FIELD1,
  (
    SELECT SUM(FIELD2) FROM table3
    WHERE table3.JOBINST_ID = table2.JOBINSTID
  )
FROM table2
WHERE EXISTS (
  SELECT * FROM table1
  WHERE table1.JOBINSTID = table2.JOBINSTID
)

Given what you've said, it's hard to tell what you want, because you haven't described which relationships are 1-1 and which are many-1, or indicated which columns are unique in which tables.

Upvotes: 1

Teja
Teja

Reputation: 13534

Try this.It should work.

SELECT FIELD1, sum(FIELD2)
FROM table1 t1,table2 t2,table t3
WHERE.t1.JOBINSTID = t2.JOBINSTID
AND   t1.JOBINSTID = t3.JOBINST_ID
GROUP BY FIELD1;

PS: Give alias to the FIELD1,FIELD2 for which table you are using in SELECT CLAUSE.

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40319

What table is FIELD2 from? Guessing here: presumably, you want to sum each individual instance of FIELD2, but the query/data may be such that you are getting multiple instances of each FIELD2, due to a one-to-many relationship within your data. If so, the fix would be to revise your query so factor out duplicates of FIELD2.

Upvotes: 0

Related Questions