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.01.0100.0Styling 0

    39.02.0100.0Non Surgical 0

    40.02.0105.0Nails 0

    41.02.0110.0Wax/ Electro 0

    DEPARTMENTS

    -----------42

    38.0100.0Cut/Finish 043

    39.0100.0CACI Face 044

    39.0105.0CACI Eyes 045

    39.0110.0CACI Body 0

    BUSINESS

    --------

    1.0

    100.0Hair 02.0

    105.0Beauty 029.0

    115.0Sun Bed 030.0

    120.0Complimentary 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 = 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

    Cheers,

    - 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