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?

Oy, that Hungarian Notation (en.wikipedia.org/wiki/Hungarian_notation) is killing my eyes. — Yuck
– Yuck, Commented Dec 7, 2016 at 16:49
this isn't my sql query, I just need help understanding it @Prdp — Matas
– Matas, Commented Dec 7, 2016 at 16:51
@SQLChao The thing I don't understand is the DATEADD(YEAR, - 2, GETDATE())) part in particular — Matas
– Matas, Commented Dec 7, 2016 at 16:55

Charles Bretana · Accepted Answer · 2016-12-07 17:13:36Z

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);

SQLChao · Accepted Answer · 2016-12-07 16:58:14Z

0

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.

answered Dec 7, 2016 at 16:58

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

3 Comments

Matas Over a year ago

Thank you for the help, this makes sense, could you please explain this? "WHERE (tblBigClubs.fldBigClubID IN (SELECT DISTINCT..."

SQLChao Over a year ago

@MattEllis They are trying to get all the BigClubs but filter on clubs that have ordered 30 or more items in the last 2 years

Matas Over a year ago

It is okay now thank you, the below answer has made it easier to understand.

Collectives™ on Stack Overflow

Guidance on understanding SQL query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related