Inner/Outer Join Help - 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

Inner/Outer Join Help

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-03-06 : 12:47:43

Below is my query. I am a relative novice to SQL. I'd like to rewrite this with joins. All should be inner joins except for the last one Aritem to shmast. That should be a left outer join because not all of our invoices (in the Aritem tables) have actually been shipped.

How would I do this? I have already read through 2 SQL books, but the examples they give are much simpler than what I need to do. Here's the Query:

SELECT DISTINCT Ardist.fcacctnum, Ardist.fcrefname, Ardist.fccashid,
Ardist.fcstatus, Armast.fcinvoice, Armast.fbcompany, Armast.fcustno,
Ardist.fddate, Ardist.fnamount * -1 as fnAmount,
glmast.fcdescr,
shmast.fcstate,
slcdpm.fcompany as CompanyName
FROM ardist, glmast, armast, slcdpm, shmast, aritem
WHERE Glmast.fcacctnum = Ardist.fcacctnum
AND Armast.fcinvoice = SUBSTRING(Ardist.fccashid,8,20)
AND Ardist.fnamount <> 0
AND ((Ardist.fcrefname = 'INV' OR Ardist.fcrefname = 'CRM' OR Ardist.fcrefname = 'VOID')
AND Glmast.fccode = 'R')
AND armast.fcustno = slcdpm.fcustno
AND armast.fcinvoice = aritem.fcinvoice
AND left(aritem.fshipkey,6) = shmast.fshipno

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-06 : 13:40:11

Here's an example:

SELECT DISTINCT t1.ColumnB, t1.ColumnE, t1.ColumnZ, ...
FROM SomeTable1 t1
INNER JOIN SomeTable2 t2
ON t1.ColumnA = t2.ColumnA
INNER JOIN SomeTable3 t3
ON t2.ColumnC = t3.ColumnC
LEFT OUTER JOIN SomeTable4 t4
ON t1.SomeColumnH = t4.SomeColumnH
WHERE t3.ColumnX <> 0

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-03-06 : 14:52:30

Thanks Tara. I got it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources