Using Joins

[cadab]

I've got two tables, customers and license, i am trying to run a query that will get the customer and license data, the two tables both have a matching customer ID. One customer ID can be in the license db several times, i.e. for trial and suspended licenses.

I want to be able to get the customer details with the most recent license aswell, i.e. get the license that has the latest expiry date.

Thanks,
James

[ssnaik84]

It would be better if u give table schema..

SELECT * FROM
customer ct LEFT JOIN license lt
ON ct.customerID=lt.customerID
ORDER BY lt.ExpiryDate DESC

[nbiswas]

You can even try this(I have given 2 queries)

SET DATEFORMAT dmy
declare @tblcust table(custid int, cust varchar(20))
insert into @tblcust select 1,'cust1' union all select 2,'cust2' union all
select 3,'cust3' union all select 4,'cust4' union all select 5,'cust5'

declare @tblLisence table(custid int, lisensekey varchar(20),expirydate datetime)
insert into @tblLisence select 1,'lisensekey1','01/01/2009' union all select 1,'lisensekey2','01/02/2009' union all
select 2,'lisensekey3','01/03/2009' union all select 5,'lisensekey4','01/04/2009' union all select 5,'lisensekey5','01/05/2009'


1) select c.*,l.* from @tblcust c , @tblLisence l where c.custid  = l.custid order by l.expirydate desc

2) select c.*,l.* from @tblcust c inner join @tblLisence l on c.custid  = l.custid order by l.expirydate desc