[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

SQL Query - Retrieving Data from Multiple Tables

Good Day Experts!

I am having a bit of trouble with a recent request for data from our database.  SQL query writing is not my strong suit so please bear with me.  

I have data in my main table A. I have a field that I can go to another table to get address information.  Seems easy enough.  Problem is that when I go to get the address information my record count increases.  I thought it would get the address data and put in on the same record as the main table information.  Here is the query:

Select
      [Carrier Name],
      SCAC,
      [Main Contact Phone],
      [Fax Number],
      [Website],
      [Carrier Type]
FROM
      [Carrier Profiles]

I get 17,519 records.  Then I try to get address information with qury below and I get 18,634 records.  Here is that query:

Select
      A.[Carrier Name],
      A.SCAC,
      B.[Address 1],
      B.[Address 2],
      B.Zip,
      A.[Main Contact Phone],
      A.[Fax Number],
      A.[Website],
      A.[Carrier Type]
FROM
      [Carrier Profiles] A, [Carrier Remittance Addresses] B
WHERE
      A.SCAC = B.SCAC

What am I doing wrong?

Thanks,
jimbo99999
0
Jimbo99999
Asked:
Jimbo99999
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that means for some records in your carrier records table, you don't have a matching record in the adress table.
try a left join:
Select
      A.[Carrier Name],
      A.SCAC,
      B.[Address 1],
      B.[Address 2],
      B.Zip,
      A.[Main Contact Phone],
      A.[Fax Number],
      A.[Website],
      A.[Carrier Type]
FROM
      [Carrier Profiles] A

LEFT JOIN [Carrier Remittance Addresses] B
ON
      A.SCAC = B.SCAC 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and to find the records without the matching records:

Select
      A.[Carrier Name],
      A.SCAC,
      B.[Address 1],
      B.[Address 2],
      B.Zip,
      A.[Main Contact Phone],
      A.[Fax Number],
      A.[Website],
      A.[Carrier Type]
FROM
      [Carrier Profiles] A

LEFT JOIN [Carrier Remittance Addresses] B
ON
      A.SCAC = B.SCAC 
                                            
WHERE B.SCAC IS NULL 

Open in new window

0
 
Jimbo99999Author Commented:
I missed the Left Join...thanks for helping with the "mind vacation".

jimbo99999
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now