Post reply

Building a view to get summary data from multiple tables

  • February 12, 2009 at 6:44 am

    #128729

    Hi All,

    I am trying to build a summary report for companies, when ever the company name is entered it shows all the details of company and the plants assosiated to companies.

    I am using two tables 'Company And Plant' they are linked by the ids as: 'comp_companyid = plnt_companyid'.

    Heres my query:

    select *

    from company

    left outer join

    plant on plnt_companyid=comp_companyid

    where comp_name='PETRODAR OPERATING COMPANY LTD'

    and comp_deleted is null

    and plnt_deleted is null

    This query run perfectly fine when there are multiples plants associated with company regardless any plant is even deleted, but when there is only one plant against the company and if that is deleted then it is not showing the company details, the report is blank, how can i over come this error, what additional condition i can write so that even if the company has single plant and that too deleted then also it gets the details of company.

    Kindly help.

  • February 12, 2009 at 7:38 am

    #942857

    just move your plant condition to the join.

    select *

    from company

    left outer join

    plant on plnt_companyid=comp_companyid and plnt_deleted is null

    where comp_name='PETRODAR OPERATING COMPANY LTD'

    and comp_deleted is null

  • February 12, 2009 at 8:57 am

    #942944

    Just to expand on what antonio said. By having the criteria plnt_deleted is null in the WHERE clause essentially changes your LEFT OUTER JOIN to an INNER JOIN. So when you use an LEFT OUTER JOIN any criteria applied to the RIGHT table, plant in your case, needs to be included in the JOIN.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 14, 2009 at 2:50 pm

    #944160

    Wow, this worked....

    Thank you all for providing the solution as well as for explanation, which was helpfull to great extent.

  • February 14, 2009 at 3:01 pm

    #944161

    when performing an outer join, criteria in the WHERE clause is performed after the JOIN has taken place. (this is a subtle but vital distinction.) in your case, although the outer join did include all companies, your WHERE clause removed deleted plants and thus any company records associated with them. by moving the "plnt_deleted" criteria to the JOIN clause, all companies will still be included but deleted plants won't be JOINed to the companies.

  • February 15, 2009 at 1:04 pm

    #944256

    [font="Verdana"]Thank goodness for the ANSI SQL join syntax! This sort of thing used to be a nightmare, involving parentheses all over the place.[/font]

  • February 15, 2009 at 6:48 pm

    #944299

    But, I liked the parenthesis... 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 15, 2009 at 7:01 pm

    #944301

    [font="Verdana"]...go program in lisp then. looks disgusted :P[/font]

  • February 16, 2009 at 10:20 am

    #944708

    Heh... well said. Actually, I hated the old outer join notation... especially since Oracle and SQL Server did it exactly the opposite from each other.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 16, 2009 at 1:07 pm

    #944807

    [font="Verdana"]Doing a full outer join was a nightmare (union of left and right outer joins) and doing a full cartesian product, er, cross join, was far too easy. The number of times I can remember a production report server being brought to its knees because some "developer" wrote some SQL and forgot to put any join criteria in the where clause...[/font]

  • February 16, 2009 at 1:31 pm

    #944824

    Bruce W Cassidy (2/16/2009)

    [font="Verdana"]Doing a full outer join was a nightmare (union of left and right outer joins) and doing a full cartesian product, er, cross join, was far too easy. The number of times I can remember a production report server being brought to its knees because some "developer" wrote some SQL and forgot to put any join criteria in the where clause...[/font]

    Agreed on all points. And, it's still way to easy to write "near cartesian" joins as well as triangular joins. I had one developer write one recently... his criteria wasn't quite right and he joined in such a fashion as to cause a many-to-many join of 2 million rows with 700 thousand rows. Even the good structure of ANSI joins can't prevent accidents or plain stupidity. And, yeap, that bad boy brought production to it's knees... especially when TempDB expanded to more that 200GB before it ran out of growth space.

    Now THAT was a nightmare.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

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