SQL Multi-Table Question

• Leslie-202186

  SSC Enthusiast

  Points: 174

  More actions

  February 18, 2005 at 2:41 pm

  #76242

  I have 3 tables:

  SOHEADER (sales order header info)

  SOLINE (sales order line-item info) and

  XCCTRAN (credit card transaction table).

  SOHEADER is outer joined 1:* SOLINE

  SOHEADER is outer joined 1:* XCCTRAN

  The problem is because I have two 1:* joins (All joins on the same OrdNbr field), my line-item detail is duplicated if there are more than 1 credit card table entry for the join on order number.

  I want to basically just pull the first XCCTRAN record that exists.  Any ideas on how to do the select on XCCTRAN to pull just the "first" transaction XCCTRAN if there are multi-transactions? 

  Thanks,

  Leslie

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  February 18, 2005 at 2:44 pm

  #543185

  Cross-posted: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=162833

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  February 18, 2005 at 3:02 pm

  #543187

  You can try something along the lines: 

  SELECT x, y, z

  FROM SOHEADER HDR

       JOIN SOLINE LINE ON( HDR.OrdNbr = LINE.OrdNbr)

       JOIN (SELECT TOP 1 TRAN.OrdNbr FROM XCCTRAN TRAN

                 INNER JOIN SOHEADER SHDR ON TRAN.OrdNbr = SHDR.OrdNbr) XCC ON( HDR.OrdNbr = XCC.OrdNbr)

   

  I wasn't born stupid - I had to study.