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..

Expand|Select|Wrap|Line Numbers

 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)

Expand|Select|Wrap|Line Numbers

 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

Using Joins

re: Using Joins

re: Using Joins

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about