Query help

My table are

Customer: customerId ,name

Order: orderId, customerId, product,date

I want to display all of the customer which have order or not. I want display name, product,date . If the customer do not order I want display only customer name.For example:

Name Product Date

John Video 09/20/2007

Mary -- ----

How can I write sql or sp?

I suggest you do some reading on SQL and joins in particular as this is something that you should learn so you can write these queries yourself.

DECLARE @.CUSTOMERTABLE (customeridint IDENTITY(1,1),name varchar(20))DECLARE @.ORDERSTABLE (orderidint IDENTITY(1,1), customeridint, productvarchar(20), orderdatedatetime)INSERT @.CUSTOMERVALUES ('Fred')INSERT @.CUSTOMERVALUES ('Joe')INSERT @.ORDERSVALUES (1,'Video',GetDate())SELECT c.name, o.product, o.orderdateFROM @.CUSTOMER cLEFTOUTER JOIN @.ORDERS oON o.customerid = c.customerid

|||

use left join instead of inner join

select cust.customerId ,cust.name,ord.orderId, ord.customerId, ord.product,ord.date from Customer cust left outer join orders ord on

cust.CustomerId = ord.CusomerId

|||

Hi,

I think you have to create a cross-tab query, it's ilttle tricky but interesting.

Check these following links

http://www.databasejournal.com/features/mssql/article.php/3521101

http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html

or you can adopt the following solution

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829,00.html

Regards,

Sandeep

|||

ASP.NET Dev:

I think you have to create a cross-tab query,

That's not necessary as they aren't pivoting any data (or at least that doesn't appear to be the case based on their description).

|||

Ozo:

How can I write sql or sp?

you can write sql as..

select customer.name, order.product, order.orderdate from customer left outer join order on customer.customerid = order.customerid

and sp as..

CREATE PROCEDURE [dbo].[usp_CustomerOrder]
AS
BEGIN
SET NOCOUNT ON
select customer.name, order.product, order.orderdate from customer leftouter join order on customer.customerid = order.customerid
END

|||

Tahnk you for your helping.

|||

Ozo:

Tahnk you for your helping.

You should also mark all the posts that helped by using the "Mark As Answer" link so that future readers with the same problem will know which methods to use.

|||

I have a new question I want to display the latest order from the customer .Can you help me?

|||

Yes, but please start a new question if you have something else to ask as it helps keep the forum tidy and easier to search.