INNER JOINS
-
Hi,
I have a transactions table which stores info on what a customer bought, how much they spent and when they bought it etc.
The field which records the amount spent is "rec_pr_total" there is also a field called "rec_dept" which records the department the transaction occurred in. There are 3 other tables:
Departments
Workgroups
Business
The departments table identifies single departments, the workgroups identifies a collection of departments and the business table identifies a collection of workgroups.
The keys for each table are as follows:
Departments: rec_dept, group_id
Workgroups: group_id, business_id
Business: business_id
The departments table relates to the transactions table via the rec_dept, the workgroups table relates to the departments table via the group_id, and the business table relates to the workgroups table via the business_id. This then allows me to find how much has been spent per business_id etc.
What I want to do is write a query to find how much has been spent under business_id 1 for example. I have written a query below to do it but it is not calculating the amount properly, it is producing a massive figure. I think I have not joined the tables correctly in the query? Can anyone help?
My query is as follows:
select sum(transaction_new.REC_PR_TOTAL)
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
Cheers
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!
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! -
At one look the Query Seems ok.
Can U paste some sample data for the tables.
That would be helpful.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself. -
Is there a way to paste portions of tables into the forum?
If I have to type out some sample data itll take me yeears!
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!
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! -
Just execute statement on the query analyzer and copy and paste the output.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself. -
Hi Andrew,
quote:
Is there a way to paste portions of tables into the forum?If I have to type out some sample data itll take me yeears!
I haven't tried this myself, but you could change Query Qutput in QA to 'Results in Text' and paste this into your post.
Wonder what this will look like ?
Btw, because you use INNER JOIN all matching rows are returned. If there aren't any matching rows, nothing is returned. Are you sure, you have matching rows?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
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......
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!
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! -
From you example, NO match on the third table.
Might you should consider using LEFT (OUTER) JOIN ?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
>>Might you should consider using LEFT
>>(OUTER) JOIN ?
would that be in each instance or just to join the 3rd table?
Cheers,
Frank
[/quote]
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!
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! -
quote:
would that be in each instance or just to join the 3rd table?
in your case it seems just to join the third table
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
So you reckon if I replace INNER JOIN with LEFT (OUTER) JOIN it may correct my problem?
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!
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! -
quote:
Hi,I have written a query below to do it but it is not calculating the amount properly, it is producing a massive figure.
My query is as follows:
If inner join was the problem then you should have not got any record.
However it is mentioned that you are getting some massive figure.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself. -
quote:
So you reckon if I replace INNER JOIN with LEFT (OUTER) JOIN it may correct my problem?
I can't tell if this will solve you problem.
Taken from BOL
quote:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join
so if you can't tell for sure whether you have matching row in ALL joined tables you should use some kind of OUTER JOIN to display rows from all rows from parent instance and matching rows from child instances
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Just reread your original post.
What results are you getting in the SUM()?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
OK, have played around with OUTER JOIN but the result is still too huge.....
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!
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! -
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 = 1If 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 = 1Then 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 = 1Cheers,
- Mark
Cheers,
- Mark
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply