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
2 1133
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Prem |
last post by:
Hi,
I am having many problems with inner join. my first problem is :
1) I want to know the precedance while evaluating query with multiple
joins.
eg.
select Employees.FirstName,...
|
by: Andy |
last post by:
Hi All!
We are doing new development for SQL Server 2000 and also moving from
SQL 7.0 to SQL Server 2000.
What are cons and pros for using IDENTITY property as PK in SQL SERVER
2000?
Please,...
|
by: tekanet |
last post by:
Hello folks,
first of all I really don't know how you gurus call this way of
writing joins:
SELECT
A.FIELD,
B.FIELD
FROM
TABLE_A A,
TABLE_B B
|
by: jbm05 |
last post by:
Hi,
I'm curious about the computational complexity of a query I have. The
query contains multiple nested self left joins, starting with a simple
select, then doing a self left join with the...
|
by: michaelnewport |
last post by:
Greetings,
I like to write my inner joins as below,
but someone at work tells me its not as 'performant'
as using the 'inner join' statement.
Is this true ?
Is there a better way to write it...
|
by: Andrzej |
last post by:
Used to read newsgroup for answers, now have to ask for them as well.
I have an application (C#, .NET 1.1) that connects to local db on MSDE
2000 SP3a (using ADO from MDAC 2.71) on one side and...
|
by: Ilan Sebba |
last post by:
When it comes to adding records in related tables, Access is really smart.
But when I try to do the same using ADO, I am really stupid.
Say I have two parent tables (eg Course, Student) and one...
|
by: serge |
last post by:
Is it generally or almost always better to have multiple
small SPs and functions to return a result set instead of
using a single big 1000+ lines SP?
I have one SP for example that is 1000+...
|
by: yasinirshad |
last post by:
Hi.. how to use joins for 4 tables.. in my query i have CALLS,STATUS,USERS,MESSAGES (4 tables).
Query:
"SELECT CALLS.CALL_ID, REQUESTOR, USERS.USER_NAME REQUESTOR_NAME, DESCRIPTION, TYPE, SCOPE,...
|
by: TC |
last post by:
I've used Access for many years. Several times, I've encountered a bug
which I refer to as the "Vanishing Joins" bug. When it happens, joins
vanish randomly from queries. More specifically, all...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |