SOLVED

Insert in a field a sequential number for each row in two tables

%3CLINGO-SUB%20id%3D%22lingo-sub-3070295%22%20slang%3D%22en-US%22%3EInsert%20in%20a%20field%20a%20sequential%20number%20for%20each%20row%20in%20two%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3070295%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%20I%20am%20new%20to%20this%20topic%2C%20please%20help%20me%20with%20the%20following.%20Thank%20you%20very%20much%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20ROW_NUMBER()%20OVER%20I%20am%20trying%20to%20insert%20in%20a%20field%20a%20sequential%20number%20for%20each%20row%20of%20two%20tables.%20This%20works%20for%20me%20if%20I%20apply%20it%20for%20all%20rows%20of%20a%20single%20table.%20How%20can%20I%20do%20it%20for%20two%20or%20more%20joined%20tables%3F%20Or%20what%20other%20function%20or%20technique%20can%20I%20use%3F%3C%2FP%3E%3CP%3EThis%20is%20the%20sentence%3C%2FP%3E%3CP%3ESELECT%3C%2FP%3E%3CP%3EROW_NUMBER()%20OVER(ORDER%20BY%20IC.IDP%20ASC)%20%5BConsecutivo%5D%3C%2FP%3E%3CP%3E%2CIC.IDP%20%5BId%20del%20P%5D%3C%2FP%3E%3CP%3E%2CIC.FECHA%20%5BFecha%20en%20tabla%20IC%5D%3C%2FP%3E%3CP%3E%2CIM.FECHA%20%5BFecha%20en%20tabla%20IM%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFROM%20TABLA_IC%20IC%3C%2FP%3E%3CP%3Einner%20join%20TABLA_IM%20IM%20ON%20IM.IDPM%20%3D%20IC.IDP%3C%2FP%3E%3CP%3Ewhere%20IC.IDP%20%3D%20106%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20only%20brings%20me%20the%20records%20of%20TABLA_IC%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wcastro_0-1643040571946.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341889i85657ACA18F9545E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22wcastro_0-1643040571946.png%22%20alt%3D%22wcastro_0-1643040571946.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBut%20I%20want%20to%20get%20the%20records%20from%20both%20tables%20TABLA_IC%20and%20TABLA_IM.%20So%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wcastro_1-1643040616606.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341892iDBAFEAB2CE4A7600%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22wcastro_1-1643040616606.png%22%20alt%3D%22wcastro_1-1643040616606.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3070295%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBusiness%20Intelligence%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%20Warehouse%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3071938%22%20slang%3D%22en-US%22%3ERe%3A%20Insert%20in%20a%20field%20a%20sequential%20number%20for%20each%20row%20in%20two%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3071938%22%20slang%3D%22en-US%22%3ETry%3CBR%20%2F%3E%3CBR%20%2F%3EFROM%20TABLA_IM%20IM%3CBR%20%2F%3Eleft%20join%20TABLA_IC%20IC%20ON%20IM.IDPM%20%3D%20IC.IDP%3CBR%20%2F%3Ewhere%20IC.IDP%20%3D%20106%3C%2FLINGO-BODY%3E
New Contributor

Good morning, I am new to this topic, please help me with the following. Thank you very much

 

With ROW_NUMBER() OVER I am trying to insert in a field a sequential number for each row of two tables. This works for me if I apply it for all rows of a single table. How can I do it for two or more joined tables? Or what other function or technique can I use?

This is the sentence

SELECT

ROW_NUMBER() OVER(ORDER BY IC.IDP ASC) [Consecutivo]

,IC.IDP [Id del P]

,IC.FECHA [Fecha en tabla IC]

,IM.FECHA [Fecha en tabla IM]

 

FROM TABLA_IC IC

inner join TABLA_IM IM ON IM.IDPM = IC.IDP

where IC.IDP = 106

 

Which only brings me the records of TABLA_IC

wcastro_0-1643040571946.png

But I want to get the records from both tables TABLA_IC and TABLA_IM. So

wcastro_1-1643040616606.png

 

 

 

 

 

 

2 Replies
best response confirmed by wcastro (New Contributor)
Solution
Try

FROM TABLA_IM IM
left join TABLA_IC IC ON IM.IDPM = IC.IDP
where IC.IDP = 106