left join causing results to be multiplied

Question

left join causing results to be multiplied

profoundhypnotic

SSC Enthusiast

Points: 188
More actions
January 21, 2022 at 7:37 pm

#3975851
Hello,
Hello,
the query below is giving me some trouble. the results of the below query are being multiplied. If I run this query on each table (v_job_operations and v_job_operations_wc) I get 10 results which is correct. but I join them like the below, I either get a result with 100 rows returned instead of 10 or I get 30 rows returned if I have distinct in the query (image below). can anyone help me identify my mistake please?
```
select distinct v_job_operations.seq, v_job_operations.description, v_job_operations.part, v_job_operations.router, 
v_job_operations.router_seq, v_job_operations_wc.workcenter
from v_job_operations
left join v_job_operations_wc
on v_job_operations.job = v_job_operations_wc.job
where v_job_operations.job = 'M00208' and v_job_operations.suffix = '000'
```

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2159 More actions · Answer 1

In which table is the 'job' column a unique key? Probably v_job_operations. The only column being SELECT'ed from v_job_operations_wc is 'workcenter'. It appears there are 3 different 'workcenter' values in v_job_operations_wc for each (unique) 'job' value in v_job_operations. How to decide which row or aggregation from v_job_operations_wc for each 'job' value? Also, please use table aliases. Aliases make the code readable

select /*distinct*/ jo.seq, jo.[description], jo.part, jo.router, 
       jo.router_seq, jow.workcenter
from v_job_operations jo
     left join v_job_operations_wc jow on jo.job = jow.job 
     /* the join likely causes row duplication */
where jo.job = 'M00208' 
      and jo.suffix = '000';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Considering the leading "v_", those probably aren't tables you're dealing with. They're probably views and the probably have some problems of the own that are at least exacerbating the issue you're having.

Without any other information on the objects being used, we can only "get lucky" with a possible solution. What would also help are the results of your individual queries formed in a readily consumable manner. Please see the article at the first link in my signature line below for how to do such a thing to help us help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101103 More actions · Answer 3

Using a sub-SELECT should help, even if the main "table" is actually a view.

SELECT job_op.seq, job_op.description, job_op.part, job_op.router, job_op.router_seq, 
    (SELECT /*TOP (1)*/ job_op_wc.workcenter 
     FROM v_job_operations_wc job_op_wc 
     WHERE job_op.job = job_op_wc.job) AS workcenter
FROM v_job_operations job_op
WHERE job_op.job = 'M00208' AND job_op.suffix = '000'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

profoundhypnotic SSC Enthusiast Points: 188 More actions · Answer 4

The sub -select gave me an Invalid row count in sub query error. I tried googling that error but it was all related to queries that are trying to count something. I should also mention that this is pervasive sql.

ScottPletcher SSC Guru Points: 101103 More actions · Answer 5

Yeah, really should have mentioned that Pervasive thing to begin with :-). That means there are multiple rows being returned for each lookup. Which one you get will be random, but as long as all of them are the same, it won't matter. It seems as if the TOP is valid for Pervasive:

...

(SELECT TOP 1 job_op_wc.workcenter

FROM v_job_operations_wc job_op_wc

WHERE job_op.job = job_op_wc.job) AS workcenter

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".