SQL Inner Join customers with orders

Question

if I have a two tables (one of customers, with their information including address, name, emails etc) and another of orders (with order number, shipping date, customer name who ordered that item), how could I show the email of the customers who have less than 3 orders?

I know I have to use an inner join and some alias's, but i'm not sure how to proceed.

Thanks!

what I have so far:

SELECT customer.email 
FROM customer as cust 
INNER JOIN (select customer_id, sum(line_qty) AS total 
            from orders as o ON cust.customer_id = o.customer_id 
            where total = (SELECT total < 3 
                           FROM (select customer_id, sum(line_qty) AS total 
                                 from orders as o ON cust.customer_id = o.customer_id
                                ) as sub);

Answer 1

I have created the full example with SQL. Just run the query to create the database, tables, and the Stored Procedure "Get Customer Orders".

There are sample data in the two table of "Customers" and the table "Orders" the relation is "1 Customer to MANY Orders" so there is a Foreign key for the Customer inside table Orders, to identify which customer had did the order. So.

First Create the Data base, Run this query.

Create DataBase [Customer_OrdersDB]

Refresh the Server explorer, you will find a database with that name has created. Then Run the Query to create Stored Procedure and Tables.

    USE [Customer_OrdersDB]
GO
CREATE PROCEDURE [dbo].[GetCustomer_Mail] 
AS
BEGIN
select Email as Customer_Mail 
from Customers as cust inner join Orders as ord
on cust.CustomerId = ord.OrderCustomerId
group by(Email)
having COUNT(ord.OrderCustomerId) < 3
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [nvarchar](50) NULL,
    [Address] [nvarchar](50) NULL,
    [Email] [nvarchar](50) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Orders]    Script Date: 12/6/2014 5:19:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [OrderDate] [datetime] NULL,
    [OrderNumber] [nvarchar](50) NULL,
    [OrderCustomerId] [int] NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Customers] ON 

INSERT [dbo].[Customers] ([CustomerId], [CustomerName], [Address], [Email]) VALUES (1, N'Ahmed', N'Cairo', N'[e-mail]')
INSERT [dbo].[Customers] ([CustomerId], [CustomerName], [Address], [Email]) VALUES (2, N'Ali', N'Paris', N'[e-mail]')
INSERT [dbo].[Customers] ([CustomerId], [CustomerName], [Address], [Email]) VALUES (3, N'Samir', N'UK', N'[e-mail]')
SET IDENTITY_INSERT [dbo].[Customers] OFF
SET IDENTITY_INSERT [dbo].[Orders] ON 

INSERT [dbo].[Orders] ([OrderId], [OrderDate], [OrderNumber], [OrderCustomerId]) VALUES (1, CAST(0x0000A2A600000000 AS DateTime), N'1234', 1)
INSERT [dbo].[Orders] ([OrderId], [OrderDate], [OrderNumber], [OrderCustomerId]) VALUES (2, CAST(0x0000A2C700000000 AS DateTime), N'555', 1)
INSERT [dbo].[Orders] ([OrderId], [OrderDate], [OrderNumber], [OrderCustomerId]) VALUES (3, CAST(0x00009CF100000000 AS DateTime), N'56d66', 1)
INSERT [dbo].[Orders] ([OrderId], [OrderDate], [OrderNumber], [OrderCustomerId]) VALUES (4, CAST(0x00009E9B00000000 AS DateTime), N'555we', 2)
INSERT [dbo].[Orders] ([OrderId], [OrderDate], [OrderNumber], [OrderCustomerId]) VALUES (5, CAST(0x0000A2A600000000 AS DateTime), N'1234', 1)
INSERT [dbo].[Orders] ([OrderId], [OrderDate], [OrderNumber], [OrderCustomerId]) VALUES (6, CAST(0x0000A2C700000000 AS DateTime), N'555', 1)
SET IDENTITY_INSERT [dbo].[Orders] OFF
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([OrderCustomerId])
REFERENCES [dbo].[Customers] ([CustomerId])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
GO

Then to access your Stored procedure that you want to get the Customer Email if he/she did orders less than 3 Orders.

1. Go to Server/Object explorer in SQL server.
2. Select your data base of name [Customer_OrdersDB].
3. Select "Programbility".
4. Select "StoredProcedures".
5. Right Click on your storedprocedure "GetCustomer_Mail" and then select Execute.

Answer 2

Try this:

SELECT c.email 
FROM customer AS c 
LEFT OUTER JOIN orders AS o ON c.customer_id = o.customer_id 
GROUP BY c.email 
HAVING SUM(o.line_qty) < 3