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'

    query results

  • 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)
    Intro to Tally Tables and Functions

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

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