Query Help

[Turing]

Customer: customerId, customerName
Order: orderId,customerId,product, date, productDefinetion

I want to display the order which is the latest order from the customer and
I want to display all customer Name
Example:

customerName product productDefinetion
John Video bla bla bla
Maria ------ -----------

[hollystyles]

Untested but this should be close if not perfect. Basically you need to group up the order table to reduce it to just the most recent (max) date for each customer id (this is a sub-query) then you can join in the customer and order tables for the rest of the info (customer names, product names etc.) That's the outter query.

select 
    c.customerName, o.product, o.productDefinition, q.orderDate 
from
   (
    select
        customerId, max(date) as orderDate 
    from 
        Order 
    group by 
        customerId 
    ) q
    join customer c on q.customerId = c.customerId 
    join order o on q.customerId = o.customerId 
        and q.orderDate = o.date

[Turing]

It is not give the my mind . I wan to that

my table is
Customer Customer Id
--------- ----------------
Mary 1
Jhon 2
Anna 3

OrderId CustomerId Product ProductDesc
------- --------- --------- -----------
1 1 video bla bla
2 1 tv bla bala
3 2 video bla bla
4 2 cd bla bla

I want to see
-------
marry tv bla bla
John cd bla bla
anna

[hollystyles]

It is not give the my mind . I wan to that

Pardon ?????

I have given you enough to solve the problem. You have changed your schema, what happened to the date column? Now I only have the orderid to key off and assume it's an increasing identity field.

Using the info you provide here is the solution:

create table Customer 
(
	Customer varchar(32), 
	[Customer Id] int 
)

create table [Order] 
(
	OrderId int, 
	[CustomerId] int, 
	Product varchar(32), 
	ProductDesc varchar(32) 
)

insert into Customer (Customer, [Customer Id])	
select 'Mary', 1
union all
select 'Jhon', 2
union all
select 'Anna', 3

insert into [Order](OrderId, [CustomerId], Product, ProductDesc)
select 1, 1, 'video', 'bla bla'
union all
select 2, 1, 'tv', 'bla bala'
union all
select 3, 2, 'video', 'bla bla'
union all
select 4, 2, 'cd', 'bla bla'

select 
    c.Customer I, ISNULL(o.Product,'') want, ISNULL(o.ProductDesc,'') [to see]
from
   Customer c left join 
   (
    select
        [CustomerId], max(OrderId) as OrderId 
    from 
        [Order] 
    group by 
        [CustomerId] 
    ) q on q.[CustomerId] = c.[Customer Id]
    left join [Order] o on q.[CustomerId] = o.[CustomerId] 
        and q.OrderId = o.OrderId
order by 
	c.[Customer Id]

I have run this in query analyser and this is the result set:

I                                want                             to see                           
-------------------------------- -------------------------------- -------------------------------- 
Mary                             tv                               bla bala
Jhon                             cd                               bla bla
Anna                                                              

(3 row(s) affected)

So I double dare you to tell me it's not what you want !!!

The cheek of some people! :)

[iamthwee]

@ OP: You should give the correct information for your tables at the beginning when someone is trying to help you. And your second example makes no sense! Look at it again.

And while you are at it learn something. Look at the tutorials for starters.

http://www.intermedia.net/support/sql/sqltut.asp