INNER JOINS

Question

INNER JOINS

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

achowe SSCrazy Points: 2075 More actions · Answer 1

Frank...sorry, overlooked your last post...in the SUM() Im getting an obviously incorrect large number.....

If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

NPeeters SSChampion Points: 12541 More actions · Answer 2

Agree with Mark.

This is most probably an issue with your data itself. Check if there are no duplicates in the linked tables somewhere.

To easily find those, you can execute the query. For each row, where the count(*) returns a number > 1, there is something wrong and the amount will be multiplied with the number in the count.



select transaction_new.Transaction_id,

       count(*)

FROM       transaction_new

   INNER JOIN departments

      ON   transaction_new.rec_dept = departments.rec_dept

   INNER JOIN workgroups

      ON   departments.group_id = workgroups.group_id

   INNER JOIN business

      ON   workgroups.business_id = business.business_id

WHERE business.business_id = 1

GROUP BY transaction_new.Transaction_id

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Mark,

quote:

Ok, why don't you see how many rows participate in original query. Change it to:

 

select count(*)

FROM transaction_new

INNER JOIN departments on transaction_new.rec_dept = departments.rec_dept

INNER JOIN workgroups on departments.group_id = workgroups.group_id

INNER JOIN business on workgroups.business_id = business.business_id

where business.business_id = 1

If that gives you a totally unexpected number of participating rows, then work down from WORKGROUPS. Eg.

 

select count(*)

FROM workgroups 

INNER JOIN business on workgroups.business_id = business.business_id

where business.business_id = 1

Then maybe:

 

select count(*)

FROM departments 

INNER JOIN workgroups on departments.group_id = workgroups.group_id

INNER JOIN business on workgroups.business_id = business.business_id

where business.business_id = 1

aah, I like your structured problem solving approach!

I lack this sometimes

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mccork SSC-Insane Points: 22288 More actions · Answer 4

quote:
I like your structured problem solving approach!
I lack this sometimes

I thought you were German?

Cheers,

- Mark

Cheers,
- Mark

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
I thought you were German?

so this is the image we have ?????

That explains why this country is not totally ruined yet !

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Deependra Newbie Points: 6 More actions · Answer 6

Deependra

Newbie

Points: 6

August 24, 2003 at 10:58 am

#471123

Try using Group by

noeld SSC Guru Points: 96590 More actions · Answer 7

quote:
WORKGROUPS
----------
38.0 1.0 100.0 Styling 0
39.0 2.0 100.0 Non Surgical 0
40.0 2.0 105.0 Nails 0
41.0 2.0 110.0 Wax/ Electro 0
DEPARTMENTS
-----------42
38.0 100.0 Cut/Finish 0 43
39.0 100.0 CACI Face 0 44
39.0 105.0 CACI Eyes 0 45
39.0 110.0 CACI Body 0
BUSINESS
--------
1.0
100.0 Hair 0 2.0
105.0 Beauty 0 29.0
115.0 Sun Bed 0 30.0
120.0 Complimentary 0
..just 4 records from each table......

Can you Write the STRUCTURE (DDL) of these tables? It is very important to know that you are matching the right types!! because you might get duplicates (or multiple ) values due to implicit conversion hence your wrong Number at the end.

The Query Looks alright so pay attention to the Data!!

* Noel