troubleshooting Question

SQL Statment to Join Several Tables

Avatar of r270ba
r270ba asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
23 Comments1 Solution668 ViewsLast Modified:
I need to combine several tables together and I do not know the best way to go about it.  I am selecting information about sales orders and the tables I am wanting to pull from are the following:

                                               Sales Order Header (SOP10100)
      Customer Main (RM00101)------------------|---------------------Sales Order Line Items (SOP10200)
   Customer details (RMxxxx I still have to figure out this table name)

I then want to add these where statements to it...

Line Marging Check - Add check for $0.00 line items and line item margins less than 20% (from SOP10200)
Tax Percent Check - Add check for sales tax from customer tax schedule (rm00101) * document total (SOP10100)
Exmpetion Check - Add check for sales tax on tax exempt companies (RMxxx, SOP00100)
Ship To Check - Add check for ship to address id <> to '1' or 'SHIP TO' (SOP10100)
Terms Check - Add check for terms = credit card (RM00101)

I have posted some code for what I have so far.  I am having trouble with the Joins for this statement.  

What I would like to do is run each one of the "checks" and return a bit flag to state whether or not it failed or passed for each check.  I only what to return rows where there is at least one failed check.  I will then use the 1 or 0 to show a green 'check' or red 'x' in each of the check columns of the SSRS Report.

Order Number     Margin     Percent     Exemption     Terms
I001234                   x          'check'         'check'           x
I001235                   x               x                x              'check'
I001236                 'check'    'check'           x              'check'

Let me know if you have any questions.
select a.sopnumbe, a.docid, a.docdate, a.custnmbr, a.custname, a.bachnumb, c.itemnmbr, a.frtamnt, a.taxamnt, b.custname from
(select sopnumbe, docid, docdate, custnmbr, custname, bachnumb, frtamnt, taxamnt from sop10100) A
left outer join
(select custnmbr, custname from rm00101) B
on a.custnmbr = b.custnmbr
left outer join
(select sopnumbe, itemnmbr, itemdesc, uofm, unitcost, unitprce from sop10200) C
on a.sopnumbe = c.sopnumbe
/*where /*(bachnumb in(@bachnumb)) and */(a.frtamnt <= '0' or a.taxamnt <= '0' or c.unitprce <= '0') /*or ((1-(c.unitcost/unitprce))<.2)*/*/
order by sopnumbe asc
Join our community to see this answer!
Unlock 1 Answer and 23 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 23 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros