Need help on a SQL Server 2008 inner join

Hello - I'm a little rusty on my SQL, and I have an inner join that I need to modify to add an extra table and field to it, and am getting syntax errors.  I'm attaching a text file with the SQL in it, which is the SQL from the design of an existing view in the database.  I basically need to join an additional table called CUSTPACKINGSLIPJOUR to the first table in the view (SALESDATA) on the "salesid" fields, so that I can also pull a field called "packingslipid" in the view. How do I do it so that it doesn't cause duplicate lines that are unnecarry in the view?

Thanks for your help
inner-join.txt
Damian_GardnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Added the last lines in the SELECT and FROM clauses to meet this need.
btw using A, B, C, ... as table aliases is considered a poor practice, as it's not intuitive what those names represent.  Far better to use aliases c for customer, s for sales, sbc as sales bar code, you get the idea.

SELECT     
	A.ACCOUNTNUM, A.PHONE, A.OURACCOUNTNUM, A.DATAAREAID, A.RECID, B.DATAAREAID AS DATAAREAID#2, B.SALESID AS SHIPMENTID, B.SALESID, 
	B.RECEIPTDATEREQUESTED, B.PAYMMODE, B.BILLTONAME, B.BILLTOSTREET, B.BILLTOCITY, B.BILLTOSTATE, B.BILLTOZIPCODE, B.PURCHORDERFORMNUM, 
	B.CREATEDDATE1, B.ITEMID, B.EXTERNALITEMID, B.CC_CONTACTNAME, B.CUSTOMERUNIT, B.DLVMODE, B.SALESSTATUS, B.DELIVERYNAME, 
	B.DELIVERYSTREET, B.DELIVERYCITY, B.DELIVERYSTATE, B.DELIVERYCOUNTRYREGIONID, B.INVOICEACCOUNT, B.DELIVERYZIPCODE, 
	B.CUSTOMERLINENUM AS POLINENUMBER, B.BILLTOCOUNTRY, C.DATAAREAID AS DATAAREAID#3, C.ITEMBARCODE, D.DATAAREAID AS DATAAREAID#4, 
	D.SHIPDATE AS SHIPPINGDATE, D.TOTALQTYPACKED AS CARTONSQTY, D.TOTALWEIGHT, D.TOTALCARTONS, D.TOTALWEIGHT AS GROSSWEIGHT, 
	D.TOTALQTYPACKED AS TAXPACKAGINGQTY, E.DATAAREAID AS DATAAREAID#5, E.CARRIER, E.TRACKINGNUMBER, E.WMSPALLETID, E.CARTONID, 
	F.DATAAREAID AS DATAAREAID#6, F.NETWEIGHT, F.ITEMNAME,
	H.PACKINGSLIPID
FROM dbo.CUSTDATA AS A INNER JOIN
	dbo.SALESDATA AS B ON A.ACCOUNTNUM = B.CUSTACCOUNT AND A.DATAAREAID = B.DATAAREAID INNER JOIN
	dbo.SALESDATA_BARCODE AS C ON B.INVENTTRANSID = C.INVENTTRANSID AND B.DATAAREAID = C.DATAAREAID INNER JOIN
	dbo.CC_SHIPMENT_TOTALS AS D ON C.INVENTTRANSID = D.INVENTTRANSID AND C.DATAAREAID = D.DATAAREAID INNER JOIN
	dbo.CC_SHIPPINGINTEGRATIONTABLE AS E ON D.INVENTTRANSID = E.INVENTTRANSID AND D.DATAAREAID = E.DATAAREAID AND 
	D.SHIPDATE = E.PACKINGDATE AND D.DATAAREAID = E.DATAAREAID INNER JOIN
	dbo.INVENTDATA AS F ON E.ITEMID = F.ITEMID AND E.DATAAREAID = F.DATAAREAID INNER JOIN
	dbo.CUSTPACKINGSLIPJOUR AS G ON G.SALESID = B.SALESID AND G.DATAAREAID = B.DATAAREAID  INNER JOIN 
	CUSTPACKINGSLIPJOUR H ON B.SALESID = H.SALESID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Damian_GardnerAuthor Commented:
Ok - great.  Yes - I agree your method for the aliases is much more clear.  I'll try this on the database and see how it works, and let you know.

thank you!
0
Damian_GardnerAuthor Commented:
BTW - the CUSTPACKINGSLIPJOUR table is in there twice, since I added the last piece onto the existing design.  Should I remove my piece of it?
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Didn't see that.  In that case, remove the 'H' line from the FROM block, and in the bottom of the SELECT block change H.PACKINGSLIPID to G.PACKINGSLIPID.

The existing JOIN is SalesiD and dataaReaID, which is different from the SalesiD posted in your question, in case that matters.
0
Damian_GardnerAuthor Commented:
I took my piece out, and it works like a charm.  thanks for your help. appreciate it.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No prob.  Thanks for the grade, good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.