left join causing results to be multiplied
-
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'
-
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
-
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) -
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) A socialist is someone who will give you the shirt off *someone else's* back.
-
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.
-
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply