By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Using Joins

P: 7
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
Oct 9 '09 #1
Share this Question
Share on Google+
2 Replies


ssnaik84
100+
P: 149
It would be better if u give table schema..

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM
  2. customer ct LEFT JOIN license lt
  3. ON ct.customerID=lt.customerID
  4. ORDER BY lt.ExpiryDate DESC
Oct 12 '09 #2

nbiswas
100+
P: 149
You can even try this(I have given 2 queries)

Expand|Select|Wrap|Line Numbers
  1. SET DATEFORMAT dmy
  2. declare @tblcust table(custid int, cust varchar(20))
  3. insert into @tblcust select 1,'cust1' union all select 2,'cust2' union all
  4. select 3,'cust3' union all select 4,'cust4' union all select 5,'cust5'
  5.  
  6. declare @tblLisence table(custid int, lisensekey varchar(20),expirydate datetime)
  7. insert into @tblLisence select 1,'lisensekey1','01/01/2009' union all select 1,'lisensekey2','01/02/2009' union all
  8. select 2,'lisensekey3','01/03/2009' union all select 5,'lisensekey4','01/04/2009' union all select 5,'lisensekey5','01/05/2009'
  9.  
  10.  
  11. 1) select c.*,l.* from @tblcust c , @tblLisence l where c.custid  = l.custid order by l.expirydate desc
  12.  
  13. 2) select c.*,l.* from @tblcust c inner join @tblLisence l on c.custid  = l.custid order by l.expirydate desc
Nov 9 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.