Data from multiple tables

• isaac.wamalwa1983

  Grasshopper

  Points: 18

  More actions

  May 22, 2012 at 10:26 am

  #258521

  Dear Experts,

  I have a scernario where i want to retrieve data from 5 tables:

  OVPM - Doc Num,Doc Type,Doc Total,Journal Remarks,Cash A/C

  VPM4 - Doc Num,Row Num,Account,Paid,Description,Acc Name, Pay type

  OACT - Account Code,Account Name

  OPCH - Doc Num,Doc type,Doc Total, Journal Remarks

  PCH1 - Doc Entry,Row Num

  I don't wish to have duplicate data.

  attached is the sample data from each table in xls

  Please kindly help me.

  Thanks,

  Wamalwa

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 22, 2012 at 10:35 am

  #1491211

  isaac.wamalwa1983 (5/22/2012)

  ---

  Dear Experts,

  I have a scernario where i want to retrieve data from 5 tables:

  OVPM - Doc Num,Doc Type,Doc Total,Journal Remarks,Cash A/C

  VPM4 - Doc Num,Row Num,Account,Paid,Description,Acc Name, Pay type

  OACT - Account Code,Account Name

  OPCH - Doc Num,Doc type,Doc Total, Journal Remarks

  PCH1 - Doc Entry,Row Num

  I don't wish to have duplicate data.

  attached is the sample data from each table in xls

  Please kindly help me.

  Thanks,

  Wamalwa

  Welcome to SSC. As you are new here, I'd like to point you to the first article in my signature block. Please take the time to read this article as it will walk you through the steps in posting the information we need to really help you.

  Also, I tried looking at one of your files, and I got an error opening it. Not sure what may be wrong.

• sk.sarim

  SSC Enthusiast

  Points: 171

  More actions

  May 24, 2012 at 4:32 pm

  #1492878

  Dear, got that you need to retrieve data from these tables, but what is the issue?

• isaac.wamalwa1983

  Grasshopper

  Points: 18

  More actions

  May 25, 2012 at 2:38 am

  #1492997

  The issue is i want help on retrieving data from those tables but without duplicating.

  Thanks.

  Isaac

• Ant-Green

  SSC Guru

  Points: 113498

  More actions

  May 25, 2012 at 3:08 am

  #1493015

  It would call for the distinct or group by clauses to be added to your query in order to ensure that each row is unique in the result set.

  You could also wrap the whole select in a ROW_NUMBER function embedded in a CTE and then select where row number = 1

  If you could look at the link which Lynn has detailed (also the second link in my signature) so that we can recreate your schema to upload the data you have provided into, we can help you out a bit more.