Sql-server – Are there any other ways to get results from Two tables without using Sub Query

sql serversubquery

I have a Customer table

+--------+---------+
| Id     |  Name   |
+--------+---------+
| 1      |   A     |
| 2      |   b     |
| 3      |   c     |
| 4      |   d     |
| 5      |   3     |
| 6      |   f     |
| 7      |   g     |
+--------+---------+

and an order table

+-----+------+--------------------------+
| ID  | C_Id | OrderDate                |
+-----+------+--------------------------+
| 1   | 1    |  2017-05-12 00:00:00.000 |
| 2   | 2    |  2017-12-12 00:00:00.000 |
| 3   | 3    |  2017-11-12 00:00:00.000 |
| 4   | 4    |  2017-12-12 00:00:00.000 |
| 5   | 1    |  2017-12-12 00:00:00.000 |
| 6   | 2    |  2017-12-12 00:00:00.000 |
| 7   | 3    |  2017-12-12 00:00:00.000 |
| 8   | 4    |  2017-11-12 00:00:00.000 |
| 9   | 2    |  2017-06-12 00:00:00.000 |
| 10  | 3    |  2017-07-12 00:00:00.000 |
+-----+------+--------------------------+

I need the result of orders with customers who did not buy in last month. That is from the order table Customer 3 and 4 have purchased in last month(November). The result should not include customer 3 and 4 even they had purchase in earlier months.

I have this query which returns the result perfectly.

SELECT C_ID , MONTH(OrderDate) from [Order] WHERE MONTH(OrderDate) <> MONTH(GETDATE()) - 1 AND C_ID NOT IN (SELECT C_ID FROM [Order] WHERE MONTH(OrderDate) = MONTH(GETDATE()) - 1)

Can anyone help me to write this query without using subquery

For better clarity, assume the Orders table contains current year orders only and I need to exclude the customers from the result(get all orders for current year) if they had any purchase in November. Which means if the customer John has an order in April, June and November the result(orders with customer name) should not include the customer John and all his orders as he made a purchase in November..

For learning purpose I need to know how can we achieve the above in other ways without using sub query.

Best Answer

I need the result of the customers who did not buy in last month.

That is from the order table Customer 3 and 4 have purchased in last month(November). The result should not include customer 3 and 4 even they had purchase in earlier months.

I am a bit confused with your question.

If you want to see customers who did not purchase last month (in this case, November) Then below query should be able to handle it.

SELECT DISTINCT
    c.id,
    c.name 
FROM #Customer c
LEFT JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1
WHERE o.c_id IS NULL

If you want to see customers who only purchased last month (in this case, November) Then below query should be able to handle it.

 SELECT DISTINCT
        c.id,
        c.name 
    FROM #Customer c
    INNER JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1

Related Solutions

Sql-server – Get Date Limitation

Try this procedure instead:

CREATE PROCEDURE dbo.spGetAdminTotalYTD_AB -- always use schema prefix!
  @Begin_Date  DATETIME, 
  @End_Date    DATETIME,
  @program_id  INT = NULL -- use a name consistent with the column
AS
BEGIN
  SET NOCOUNT ON; -- always use SET NOCOUNT ON and semi-colons

  DECLARE @year INT = 2014; -- why should this be hard-coded? 
                     -- So you can refactor the code in January? 
                     -- And again the following January?

  -- let's get the beginning of this year instead:
  DECLARE @thisyear DATETIME = DATEADD(DAY, 
    1-DATEPART(DAYOFYEAR, GETDATE()), DATEDIFF(DAY,0,GETDATE()));

  -- if @Begin_Date pre-dates that, make it Jan 1:
  DECLARE @startrange DATETIME = CASE
    WHEN @Begin_Date < @thisyear THEN @thisyear ELSE @Begin_Date END;

  -- make sure the end of the range is actually at the
  -- end of the month *before* the end date they actually
  -- specify. Assumes they're basing this on GETDATE().
  -- If they pass the last day of the month, you may need
  -- to add logic to make that a valid selection.
  DECLARE @endrange DATETIME = DATEADD
  ( 
    DAY, 
    1-DATEPART(DAY, DATEADD(DAY, DATEDIFF(DAY,0,@End_Date),0)), 
    DATEADD(DAY, DATEDIFF(DAY,0,@End_Date), 0)
  );

  ;WITH x AS
  (
    SELECT company_id, dealer_id, 
      [Jan],[Feb],[Mar],[Apr],[May],[Jun],
      [Jul],[Aug],[Sep],[Oct],[Nov],[Dec],

      -- to group YTD by company_id + dealer_id:
      YTD = [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]
           +[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec]

      -- to group YTD by only company_id:
      YTD2 = SUM([Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]
           +[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec]) 
           OVER (PARTITION BY Company_id)
    FROM
    (
      SELECT company_id, dealer_id,
        [Jan] = COALESCE([Jan],0), [Feb] = COALESCE([Feb],0), [Mar] = COALESCE([Mar],0),
        [Apr] = COALESCE([Apr],0), [May] = COALESCE([May],0), [Jun] = COALESCE([Jun],0),
        [Jul] = COALESCE([Jul],0), [Aug] = COALESCE([Aug],0), [Sep] = COALESCE([Sep],0),
        [Oct] = COALESCE([Oct],0), [Nov] = COALESCE([Nov],0), [Dec] = COALESCE([Dec],0)
      FROM
      (
        SELECT m = LEFT(DATENAME(MONTH,DATEADD(MONTH,
                   DATEDIFF(MONTH,0,Funded_date), 0)),3),
          company_id, 
          dealer_id, 
          mc = COUNT(*)
        FROM dbo.tContract
        WHERE program_id = @program_id
          AND Funded_date >= @startrange
          AND Funded_date < @endrange -- don't use BETWEEN for range queries
        GROUP BY LEFT(DATENAME(MONTH,DATEADD(MONTH,
            DATEDIFF(MONTH,0,Funded_date), 0)),3),
          company_id, 
          dealer_id
      )
      AS x
      PIVOT 
      (
        MAX(mc) FOR m IN 
        (
          [Jan],[Feb],[Mar],[Apr],[May],[Jun],
          [Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
        )
      ) AS p
    ) AS y
  ) 
  SELECT 
    comp.name, deal.dealer_code, -- don't use meaningless aliases like a,b,c,d
    cont.last_name, cont.city, cont.[state], cont.phone, 
    x.[Jan],x.[Feb],x.[Mar],x.[Apr],x.[May],x.[Jun],
    x.[Jul],x.[Aug],x.[Sep],x.[Oct],x.[Nov],x.[Dec],
    x.YTD, x.YTD2
  FROM x
  INNER JOIN dbo.tDealer AS deal
  ON x.dealer_id = deal.dealer_id
  INNER JOIN dbo.tCompany AS comp
  ON x.company_id = comp.company_id
  INNER JOIN dbo.tContact AS cont
  ON deal.contact_id = cont.contact_id;
END
GO

Many of the comments have a lot of background, and it's not just me ranting about how you should write code like I do:

Sql-server – select from many to many by matching field within a column

Replace the ??? in your attempted query with

COUNT(DISTINCT Customer.Surname) = 1

So, the whole query, with a couple of improvements, would be

SELECT
  co.OrderID,
  CustomerCount = COUNT(distinct c.CustomerID),
  FamilyName    = MAX(c.Surname)
FROM
  dbo.CustomerOrderLink AS co
  INNER JOIN dbo.Customer AS c ON c.CustomerID = co.CustomerID 
GROUP BY
  co.OrderID
HAVING
  COUNT(DISTINCT c.CustomerID) > 1
  AND COUNT(DISTINCT c.Surname) = 1
;

An inner join would be better here since orders without associated customers would not match the COUNT(DISTINCT c.CustomerID) > 1 condition anyway. Talking of that condition, if the CustomerOrderLink table can never have more than one CustomerID/OrderID pair (and it should not), then you can replace it with COUNT(c.CustomerID) > 1 or even COUNT(*) > 1.

Note that if Customer.Surname is a nullable column and some customers do have a null there, the above may not return what you expect, because COUNT ignores nulls (and so it will return 1 if a group contains a couple of customers with the same surname and the rest without a surname).

Best Answer

Related Solutions

Sql-server – Get Date Limitation

Sql-server – select from many to many by matching field within a column

Related Question