Guidance on understanding SQL query

Question

I have been trying to create a query for the past week or so now using the following query as a reference and needed some help breaking it down, so I know what each section does.

Select Distinct
        tblCustomers.fldEmail
      , tblCustomers.fldCustomerID
      , tblCustomers.fldName
      , tblCustomers.fldContactName
From    tblBigClubs
Inner Join tblBigClubMatch
        On tblBigClubs.fldBigClubID = tblBigClubMatch.fldBigClubID
Inner Join tblCustomers
        On tblBigClubMatch.fldCustomerID = tblCustomers.fldCustomerID
Where   (tblBigClubs.fldBigClubID In (Select Distinct
                                                dbo.tblBigClubMatch.fldBigClubID
                                      From      dbo.tblOrders
                                      Inner Join dbo.tblOrderItems
                                                On dbo.tblOrders.fldOrderID = dbo.tblOrderItems.fldOrderID
                                      Inner Join dbo.tblBigClubMatch
                                                On dbo.tblOrders.fldCustomerID = dbo.tblBigClubMatch.fldCustomerID
                                      Where     (dbo.tblOrders.fldOrderDate > DateAdd(Year, -2, GetDate()))
                                                And (dbo.tblOrderItems.fldStockCode Like 'TO%')
                                      Group By  dbo.tblBigClubMatch.fldBigClubID
                                      Having    (Sum(dbo.tblOrderItems.fldQtyOrder) >= 30))
         And tblCustomers.fldEmail Is Not Null
         And dbo.tblCustomers.fldEmail <> ''
         And Len(dbo.tblCustomers.fldEmail) > 8
        );

Please can someone explain what each section is doing?

Answer 1

First of all, simplify and format:

Select Distinct c.fldEmail, c.fldCustomerID, c.fldName, c.fldContactName
From tblBigClubs b
   Join tblBigClubMatch m On m.fldBigClubID = b.fldBigClubID 
   Join tblCustomers c On c.fldCustomerID = m.fldCustomerID
Where Len(c.fldEmail) > 8
   and b.fldBigClubID In 
      (Select Distinct im.fldBigClubID
       From dbo.tblOrders o
            Join dbo.tblOrderItems i On i.fldOrderID = o.fldOrderID
            Join dbo.tblBigClubMatch im On im.fldCustomerID = o.fldCustomerID
       Where o.fldOrderDate > DateAdd(Year, -2, GetDate())
           And i.fldStockCode Like 'TO%'
       Group By  im.fldBigClubID
       Having Sum(i.fldQtyOrder) >= 30);

the above is functionally equivalent. (using aliases eliminates much clutter and makes the SQL easier to read and understand) then, examine the predicates. There are only two:

Len(c.fldEmail) > 8 -- if this is satisfied, then obviously, fldEmail cannot be null, and

The b.fldBigClubID In subquery clause. This looks like it is finding all the values of fldBigClubID where fldStockCode starts with a TO and fldOrderDate is in last two years, that have 30 or more orders in that same period.

to summarize, it appears to be getting four fields from the customers table for those customers that have 30 or more orders for items with stock codes starting with TO in the past two years.

so actually, this should also be equivalent to:

Select fldEmail, fldCustomerID, 
   fldName, fldContactName
From tblCustomers 
Where fldCustomerID In
   (Select distinct o.fldCustomerID
      From dbo.tblOrders o
        Join dbo.tblOrderItems i On i.fldOrderID = o.fldOrderID
        Join dbo.tblBigClubMatch m On m.fldCustomerID = o.fldCustomerID
    Where o.fldOrderDate > DateAdd(Year, -2, GetDate())
       And i.fldStockCode Like 'TO%'
    Group By m.fldBigClubID
    Having Sum(i.fldQtyOrder) >= 30);

Answer 2

Going over the entire query is not likely but if you have specific parts that are giving you trouble we can help. In response to your comment

DateAdd(Year, -2, GetDate())

This returns a date 2 years minus today's date. You can run

SELECT DateAdd(Year, -2, GetDate())

Returns

2014-12-07 10:56:07.290

So it looks like they are pulling customers that have ordered 30 items or more in the last 2 years.