January 24, 2013 at 7:47 am
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.
January 24, 2013 at 7:56 am
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
January 24, 2013 at 8:00 am
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
January 25, 2013 at 1:43 am
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...
January 25, 2013 at 3:51 am
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/
January 25, 2013 at 5:05 am
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