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

x
?
Solved

Join Problem

Posted on 2004-11-27
3
Medium Priority
?
411 Views
Last Modified: 2008-01-09
I have 4 tables which i need to join,

Lerg6 which is the main table and has the folowing fields
operating_company_number needs to be joined to Lerg 1 Table
administrative_ocn needs to be joined to Lerg 1 Table (independently)
rc_name_abbreviation and lata_ratecenter needs to be joined to Lerg 6 Table

the query should return all rows of lerg 6 inc the linked info from the other fields

my rowcount for lerg6 is 395056 rows

but if i run the folowing query

SELECT
  count(*) AS FIELD_1
FROM
  LERG6
  INNER JOIN LERG1 ON (LERG6.operating_company_number = LERG1.ocn)
  INNER JOIN LERG1 LERG11 ON (LERG6.administrative_ocn = LERG11.ocn)
  INNER JOIN LERG8 ON (LERG6.rc_name_abbreviation = LERG8.rc_name_abbreviation) AND (LERG6.lata_ratecenter = LERG8.lata)


my rowcount is 784026

if i only run

SELECT
  count(*) AS FIELD_1
FROM
  LERG6
  INNER JOIN LERG1 ON (LERG6.operating_company_number = LERG1.ocn)
  INNER JOIN LERG1 LERG11 ON (LERG6.administrative_ocn = LERG11.ocn)

my rowcount  is 395056 rows which is the correct one. So i am not sure what i am missing her
0
Comment
Question by:AlexPonnath
  • 2
3 Comments
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12686482
There are multiple records in LERG8 for each record in LERG6. What exactly do you want from LERG8?
0
 

Expert Comment

by:CapZilog
ID: 12686581
You can use a sub query to summmarize the contents of LERG8 for example - ie the way a custmer table relates to orders.

But it all depends on your needs.

Ron
0
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 2000 total points
ID: 12686643
To elaborate on my answer, the INNER JOIN matches all rows in LERG6 with all rows in LERG8, and returns a row in the results for each match. If there are three rows in LERG8 for one particular row in LERG6, then data from that row in LERG6 will be returned three times, once for each match in LERG8. If you are trying to find only those rows in LERG6 for which rows exist in LERG8, then you could write:

SELECT
  DISTINCT *
FROM
  LERG6
  INNER JOIN LERG1 ON (LERG6.operating_company_number = LERG1.ocn)
  INNER JOIN LERG1 LERG11 ON (LERG6.administrative_ocn = LERG11.ocn)
  INNER JOIN LERG8 ON (LERG6.rc_name_abbreviation = LERG8.rc_name_abbreviation) AND (LERG6.lata_ratecenter = LERG8.lata)

If you want to see the number of rows in LERG8 for each row in LERG6, excluding zeros, you could write:

SELECT
  LERG6.ID,
  COUNT(LERG8.ID)
FROM
  LERG6
  INNER JOIN LERG1 ON (LERG6.operating_company_number = LERG1.ocn)
  INNER JOIN LERG1 LERG11 ON (LERG6.administrative_ocn = LERG11.ocn)
  INNER JOIN LERG8 ON (LERG6.rc_name_abbreviation = LERG8.rc_name_abbreviation) AND (LERG6.lata_ratecenter = LERG8.lata)
GROUP BY LERG6.ID

If you want to see the same but including zeros, you could write:

SELECT
  LERG6.ID,
  SUM(CASE WHEN LERG8.ID IS Null THEN 0 ELSE 1 END)
FROM
  LERG6
  INNER JOIN LERG1 ON (LERG6.operating_company_number = LERG1.ocn)
  INNER JOIN LERG1 LERG11 ON (LERG6.administrative_ocn = LERG11.ocn)
  INNER JOIN LERG8 ON (LERG6.rc_name_abbreviation = LERG8.rc_name_abbreviation) AND (LERG6.lata_ratecenter = LERG8.lata)
GROUP BY LERG6.ID

and so on. But as CapZilog says, without knowing what you are trying to achieve it is hard to be more precise.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question