Member Avatar for jovillanuev

Guys,

I have 2 tables. My objective is to get the information from Table1 and place it to table2. the 2 tables have the same Itemnumber and DefectCode. How could make this in correct scripts cause when i make the script it triple the records.

TABLE1
ItemNumber---------Price---PO-------Code-Qty
P300-1710-DROID-U--302.05--9100962--506--1
P300-1710-DROID-U--302.05--9100962--553--4
P300-1710-DROID-U--302.05--9100962--505--2


TABLE2
Itemnumber---------ESN-----------------Code-COO
P300-1710-DROID-U--268435458109959871--506--MALAYSIA
P300-1710-DROID-U--268435458111803411--553--MALAYSIA
P300-1710-DROID-U--268435458115318904--553--MALAYSIA
P300-1710-DROID-U--268435458116412601--553--MALAYSIA
P300-1710-DROID-U--268435459401812963--553--MALAYSIA
P300-1710-DROID-U--268435458114918871--505--MALAYSIA
P300-1710-DROID-U--268435459402546544--505--MALAYSIA


RESULT
ItemNumber---------Price---PO-------Code-Qty--ESN-----------------COO
P300-1710-DROID-U--302.05--9100962--506--1----268435458109959871--MALAYSIA
P300-1710-DROID-U--302.05--9100962--553--4----26843545811180341--MALAYSIA
P300-1710-DROID-U--302.05--9100962--553--4----268435458115318904--MALAYSIA
P300-1710-DROID-U--302.05--9100962--553--4----268435458116412601--MALAYSIA
P300-1710-DROID-U--302.05--9100962--553--4----268435459401812963--MALAYSIA
P300-1710-DROID-U--302.05--9100962--505--2----2268435458114918871--MALAYSIA
P300-1710-DROID-U--302.05--9100962--505--2----2268435459402546544--MALAYSIA


Select t2.itemnumber, t2.esn, t2.coo, t1.price, t1.po
from table2 t2 left outer join table1 t1
on t2.itemnumber = t1.itemnumber
and t2.code = t1.code

Thank you in advance.

Regards,

Jov

  • 2 Contributors
  • 1 Reply
  • 121 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by moone009

Dani AI

Generated

A common cause of the “tripled” rows is the one‑to‑many nature of the data: Table2 holds one row per device (ESN) while Table1 holds aggregated info (price/PO and a Qty). As noted, moving a quantity of 4 to a table that stores individual ESNs will naturally produce four output rows. The real question is whether you want one output row per ESN (normal) or to treat the aggregated Table1 row as a single result (and avoid repetition).

Start by diagnosing where the multiplicity comes from: confirm how many matching rows exist per ItemNumber+Code in each table and whether Table1 accidentally contains duplicate key rows. A quick grouped query will show keys with more than one match.

If the goal is “one output row per ESN with price/PO attached,” make sure Table1 has a single row per ItemNumber+Code (or use an aggregated/derived row) and join from Table2 to that single row.

If the goal is “assign exactly Qty ESNs to each Table1 row,” use a deterministic pairing: give each ESN a row number partitioned by ItemNumber+Code, expand the Table1 Qty into 1..Qty rows (a numbers/tally source), and join on the row number so the nth ESN is paired to the nth unit. Example pattern (ROW_NUMBER + numbers/tally) is reliable for this task.

Always handle edge cases: what if there are fewer ESNs than Qty, or extra ESNs? Use transactions and mark ESNs as assigned to avoid double allocation. For details on ROW_NUMBER and CTE techniques see ROW_NUMBER (Transact-SQL) and WITH common_table_expression (Transact-SQL).

Member Avatar for moone009

Your second table seems to be an ITEM table that contains all of your items when the first Table is a new inventory table. I guess I would need to see the actual tables but it appears that table 2 contains the ESN and by federal law each mobile device has to contain and electronic serial number. So when you move P300-1710-DROID-U--302.05--9100962--553--4 it is going to place 4 records in the ITEM table.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.