Join Problem

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
AlexPonnathAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
There are multiple records in LERG8 for each record in LERG6. What exactly do you want from LERG8?
0
CapZilogCommented:
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
muzzy2003Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.