Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ankurgupta26
Starting Member
32 Posts |
 Posted - 2002-09-23 : 10:44:45
|
| The common form of joins is...after the ON statement the relation between the table that have been joined is specified SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city In the following query, after the ON statement, the relation between the a & b table is not specified. Do u know how the relation has been specified. Is there any default relation ? I couldn't find such a syntax in books online. The statements after "ON" statement seem more like part of WHERE condition. SELECT DISTINCT a.Manufacturing_Plant_Id, a.Sales_Item_Prefix, a.Sales_Item_Base, a.Sales_Item_Suffix, a.Sales_Item_Control FROM input_Explosion_error a left JOIN cbmt040_Structure b ON len(isnull(a.sales_item_base, '')) > 0 and LTRIM(RTRIM(b.no_part_base_nxt_a)) is null and len(isnull(a.Sales_Item_Suffix, ''))> 0 and LTRIM(RTRIM(b.NO_PART_SUFF_NXT_A)) is null and len(isnull(a.sales_item_prefix, '')) > 0 and LTRIM(RTRIM(b.NO_PART_PREF_NXT_A)) is null and len(isnull(a.Sales_Item_Control, '')) > 0 and LTRIM(RTRIM(b.NO_PART_CNTL_NXT_A)) is null Thanks !AnkuR. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-23 : 11:06:40
|
| yep it's strange.It will do a cartesian product of all entries in a and b that match the criteria.Being in a left join it will also include rows from a if there is no join entry due to exclusion by the conditions.Guess it's because b contains entries for each row of a (or is a mistake).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Outer Join