select - SQL Inner Join customers with orders -

if have 2 tables (one of customers, information including address, name, emails etc) , of orders (with order number, shipping date, customer name ordered item), how show email of customers have less 3 orders?

i know have use inner join , alias's, i'm not sure how proceed.

thanks!

what have far:

select customer.email  customer cust  inner join (select customer_id, sum(line_qty) total              orders o on cust.customer_id = o.customer_id              total = (select total < 3                             (select customer_id, sum(line_qty) total                                   orders o on cust.customer_id = o.customer_id                                 ) sub); 

i have created full example sql. run query create database, tables, , stored procedure "get customer orders".

there sample data in 2 table of "customers" , table "orders" relation "1 customer many orders" there foreign key customer inside table orders, identify customer had did order. so.

first create data base, run query.

create database [customer_ordersdb] 

refresh server explorer, find database name has created. run query create stored procedure , tables.

    use [customer_ordersdb] go create procedure [dbo].[getcustomer_mail]  begin select email customer_mail  customers cust inner join orders 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 ******/ 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'ahmed@yahoo.com') insert [dbo].[customers] ([customerid], [customername], [address], [email]) values (2, n'ali', n'paris', n'ali@yahoo.com') insert [dbo].[customers] ([customerid], [customername], [address], [email]) values (3, n'samir', n'uk', n'samir@msn.com') set identity_insert [dbo].[customers] off set identity_insert [dbo].[orders] on   insert [dbo].[orders] ([orderid], [orderdate], [ordernumber], [ordercustomerid]) values (1, cast(0x0000a2a600000000 datetime), n'1234', 1) insert [dbo].[orders] ([orderid], [orderdate], [ordernumber], [ordercustomerid]) values (2, cast(0x0000a2c700000000 datetime), n'555', 1) insert [dbo].[orders] ([orderid], [orderdate], [ordernumber], [ordercustomerid]) values (3, cast(0x00009cf100000000 datetime), n'56d66', 1) insert [dbo].[orders] ([orderid], [orderdate], [ordernumber], [ordercustomerid]) values (4, cast(0x00009e9b00000000 datetime), n'555we', 2) insert [dbo].[orders] ([orderid], [orderdate], [ordernumber], [ordercustomerid]) values (5, cast(0x0000a2a600000000 datetime), n'1234', 1) insert [dbo].[orders] ([orderid], [orderdate], [ordernumber], [ordercustomerid]) values (6, cast(0x0000a2c700000000 datetime), n'555', 1) set identity_insert [dbo].[orders] off alter table [dbo].[orders]  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 access stored procedure want customer email if he/she did orders less 3 orders.

1. go server/object explorer in sql server.
2. select data base of name [customer_ordersdb].
3. select "programbility".
4. select "storedprocedures".
5. right click on storedprocedure "getcustomer_mail" , select execute.