We help IT Professionals succeed at work.
Get Started

how to join tables for a single data set.Joins

856 Views
Last Modified: 2013-11-05
Hi, I need to produce a single data set of suppliers with various information about each.  There are various tables that store additional data, but I only need one record from each of them.  How do I do this???

SELECT
      AB.ABAN8 AS vSupplierID,
      RTRIM(LTRIM(MN.WWMLNM)) AS vName,
      RTRIM(LTRIM(MA.ALADD1)) AS vAddress1,
      RTRIM(LTRIM(MA.ALADD2)) AS vAddress2,
      RTRIM(LTRIM(MA.ALADD3)) AS vAddress3,
      RTRIM(LTRIM(MA.ALADD4)) AS vAddress4,
      RTRIM(LTRIM(MA.ALADDZ)) AS vPostCode,
      RTRIM(LTRIM(MA.ALCTY1)) AS vCity,
      RTRIM(LTRIM(MA.ALCTR)) AS vCountry,
      REPLACE(PN.WPAR1 + PN.WPPH1,' ', '') AS vPhone,
      REPLACE(FN.WPAR1 + FN.WPPH1,' ', '') AS vFax,
      RTRIM(LTRIM(EA.EAEMAL)) AS vEmail,
      REPLACE(AB.ABTAX,' ', '') AS vGSTNo,
      REPLACE(BK.AYTNST + BK.AYCBNK,' ', '') AS vBankAcct,
    REPLACE(AB.ABAC11,' ','') AS vPOExempt,
      AB.ABUSER AS vModifiedBy,
      CONVERT(varchar(23),CONVERT(datetime, master.dbo.J2DMY(AB.ABUPMJ), 103) +
            CONVERT(datetime, CASE LEN(AB.ABUPMT)
                  WHEN 6 THEN SUBSTRING(CAST(AB.ABUPMT AS varchar(6)), 1, 2) + ':' +
                        SUBSTRING(CAST(AB.ABUPMT AS varchar(6)), 3, 2) + ':' +
                        SUBSTRING(CAST(AB.ABUPMT AS varchar(6)), 5, 2)
                  WHEN 5 THEN '0' + SUBSTRING(CAST(AB.ABUPMT AS varchar(6)), 1, 1) + ':' +
                        SUBSTRING(CAST(AB.ABUPMT AS varchar(6)), 2, 2) + ':' +
                        SUBSTRING(CAST(AB.ABUPMT AS varchar(6)), 4, 2)
                  ELSE '00:00:00' END, 108),127) AS vModifiedDate
FROM
      DSDATA.F0101 AS AB WITH (NOLOCK) INNER JOIN
      DSDATA.F0111 AS MN WITH (NOLOCK) ON AB.ABAN8 = MN.WWAN8
--What sort of joins are required????????
      DSDATA.F0116 AS MA WITH (NOLOCK) --Mailing Addresses - from F0116 where MA.ALAN8 = AB.ABAN8 and MAX(MA.ALEFTB) [MUST RETURN ONLY ONE]
      DSDATA.F0115 AS PN WITH (NOLOCK) --Phone Numbers -from F0115 where PN.WPAN8 = AB.ABAN8 and PN.WPETP = 'BUS' and MAX(PN.WPRCK7) [MUST RETURN ONLY ONE]
      DSDATA.F0115 AS FN WITH (NOLOCK) --Fax Numbers - from F0115 where PN.WPAN8 = AB.ABAN8 and PN.WPETP = 'FAX' and MAX(PN.WPRCK7) [MUST RETURN ONLY ONE]
      DSDATA.F01151 AS EA WITH (NOLOCK) --Email Addresses -from F01151 where EA.EAAN8 = AB.ABAN8 and EA.EAETP = 'W' and MAX(EA.EARCK7)  [MUST RETURN ONLY ONE]
      DSDATA.F0030 AS BK WITH (NOLOCK) --Bank Accounts -from F0030 where BK.AYAN8 = AB.ABAN8 and BK.AYBKTP = 'V' and MAX(?????)  [MUST RETURN ONLY ONE]
WHERE (AB.ABAN8 > '25' AND AB.ABAN8 < '60')
Comment
Watch Question
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE