Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

A Refresher on Joins Expand / Collapse
Author
Message
Posted Thursday, April 26, 2007 6:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 28, 2014 9:48 AM
Points: 14, Visits: 63

Good article, especially for beginners, but useful as a simple refresher.

We discuss joins quite often in optomizing code and it's amazing how many developers forget the basic rules.

Waiting for the next article on joins though ...

Post #361417
Posted Monday, May 14, 2007 7:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 11:12 PM
Points: 13, Visits: 50
This was indeed a good refresher on joins...

What if you had to extract data from table_1 which does not exist in table_2 but there are two or more conditions...(By conditions I mean what comes after the 'on' keyword).
Post #365623
Posted Monday, May 14, 2007 11:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,397, Visits: 9,611
You just add them to the where clause (if it's on the left table).  If it's on the joined table then you have to make it part of the join clause...
Post #365708
Posted Wednesday, March 26, 2008 10:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 10:18 AM
Points: 33, Visits: 74
If this article is supposed to be for newbies, I think more narrative is needed for the explanation "Observe the query and output carefully...and then see if you can get the following output, which you will agree makes more sense."

Sorry, but that provides no explanation about why it would make more sense. You should point out the differences, how to achieve the output and the reason it makes more sense.

Post #474918
Posted Wednesday, August 27, 2008 2:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:36 PM
Points: 54, Visits: 206
Well here is a real world scenario
from
receiver_line rl
left outer join receiver r
on (r.id=rl.receiver_id)
left outer join receiver_line_del rld
on (rl.receiver_id = rld.receiver_id and rld.receiver_line_no=rl.line_no)
left outer join purc_order_line pol
on (rl.purc_order_id = pol.purc_order_id and rl.purc_order_line_no = pol.line_no)
left outer join purchase_order po
on (po.id=rl.purc_order_id)
left outer join purc_line_del pld
on (pol.purc_order_id = pld.purc_order_id and pld.purc_order_line_no = pol.line_no)
left outer join vendor v
on (po.vendor_id=v.id)

My problem is that i get dupicate records/rows when there is a pld record/row and I do not know hwo to avoid this.

The table relationships are receivers to purchase orders
There a header(1)/line(many)/line delivery(many) relationship hend the r,rl,rld and po,pol,pld

How do I avoid the duplicate records/rows when pld exist?
Each line could have many deliveries scheduled.

tia,
Post #559956
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse