Multiple Table Joins - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple Table Joins

Author

Topic

learzbu
Starting Member

27 Posts

Posted - 2011-11-13 : 16:31:47

When I run:

select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
scheme.opheadm.customer, qty_received
from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
where
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

It gives me the 5 rows that I need to work with, one column is customer (which is giving me customer code) that I want to replace with customer name from another table

So I tried:
select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
scheme.jcmastm.name, qty_received
from scheme.opheadm
join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer

where
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'

this works with the same 5 rows that i need but loops them through every customer from the table scheme.jcmastm giving me a total of 960 rows not just the 5 that i want to work with. Can anyone explain why this is looping?

learzbu
Starting Member

27 Posts

Posted - 2011-11-13 : 16:50:17

Sorry just saw my issue I should have had

join scheme.jcmastm on scheme.jcmastm.customer = scheme.opheadm.customer

not join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 00:34:33

quote:
Originally posted by learzbu

Sorry just saw my issue I should have had

join scheme.jcmastm on scheme.jcmastm.customer = scheme.opheadm.customer

not join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer

that will end up in trivial join condition (like 1=1) and cartesian project will be performed.

ALso why do you repeat schema.db.table names everywhere. why not use short aliases to make code clearer and neat?
something like


select o.order_no, p.order_no, delivery_no, invoice_no,
j.name, qty_received
from scheme.opheadm o
join scheme.porecpm p on (ltrim(rtrim(p.commnt)) like (ltrim(rtrim(o.order_no)) + '/%'))
join scheme.jcmastm j on o.customer = j.customer

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources