Joins

  • hi Team,

    am having a query for selecting 100 columns from 20 Tables.

    am using 15 inner joins,

    in some tables there are no records, but am using inner join for that table, in 20 tables if any one table with no records then the total query is null.

    in 20 tables 10 tables are not mandatory tables, if there is no data in that 10 tables also remaining 10 tables data should be displayed.

    i know only inner joins, please give some suggetion or small example....

    Please help.

  • Have a read through this link - inner joins may not be appropriate if some of your tables are empty

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • you need to learn how to use OUTER JOIN's (LEFT or RIGHT)

    SELECT t1.col1, t2.col2

    FROM table1 AS t1

    LEFT JOIN table2 AS t2

    ON t2.SomeIdCol = t1.SameIdCol

    ....

    The above would return as many records as found in table1 with related data from table2 whenever its exist. Please note: for a single table1 record you will see the same data of table1 record as many times as many related records are found in table2

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Hi Team,

    Please suggest the best join to get the records(avioiding duplicates)

    am having 20 joins.

    if particular record is not there in that table, then it should not join that table,

    like that i want to compare 20 tables.

    Please suggest...

  • Minnu (1/25/2013)


    Hi Team,

    Please suggest the best join to get the records(avioiding duplicates)

    am having 20 joins.

    if particular record is not there in that table, then it should not join that table,

    like that i want to compare 20 tables.

    Please suggest...

    JOIN's have nothing to do with avoiding duplicates.

    You cannot "JOIN" or "NOT JOIN" to table based on record from another table used in the same query.

    You can use OUTER JOIN (LEFT or RIGHT) to ensure that the records are returned when there is a related record is found in the OUTER JOINed table but it will not stop returning records from your "base" table when there is no corresponding records in the table you OUTER JOIN to.

    If you need detailed example, please follow this:

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Minnu (1/25/2013)


    if particular record is not there in that table, then it should not join that table, like that i want to compare 20 tables.

    Why ? just to make the query less-resource intensive ? then thats not possible because records existence checking will consume resources. For short run you have to bear the cost

    But Yes , if you are only checking the records existence in tables (no column involve in *select*, *group by* , *Order by* ) then you can opt for AND EXISTS. and for longer run .. database redesign in the option.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 6 posts - 1 through 6 (of 6 total)

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