June 12, 2003 at 12:25 pm
Hello All,
I need to join the results of 2 queries on the same table and then order the result set. Is the use of a derived table as shown below and acceptable method?
select
[ApplicantID]
, [HomeOwner]
, [ApplicantName]
, [ApplicantLeadId]
from
(
select
a.[ApplicantID]
, a.[HomeOwner]
, a.[ApplicantName]
, a.[ApplicantLeadId]
from
AgedLeadPlanMatchedLead alp
inner join ArchivedLeads.dbo.allAgedLeads a
on alp.applicantLeadId = a.applicantLeadid
where
alp.leadSourceId = @allAgedLeads /* lead source archived */
UNION ALL /* forces selection of all leads, does not remove duplicates */
select
a.[ApplicantID]
,a.[HomeOwner]
,a.[ApplicantName]
, a.[ApplicantLeadId]
from
AgedLeadPlanMatchedLead alp
inner join applicant a
on alp.applicantLeadId = a.applicantLeadid
where
alp.leadSourceId = @applicant /* lead source applicant */
)a
order by
a.[ApplicantName]
June 12, 2003 at 2:31 pm
Hello.
That should work, but you don't need to nest it this way. Try placing the ORDER BY clause after the second statement. Here's an example:
USE Northwind
SELECT TOP 5 CompanyName
FROM Customers
UNION
SELECT TOP 5 LastName FROM Employees
ORDER BY CompanyName DESC
In addition, you may want to take a quick look at the comments regarding the ORDER BY clause in a SELECT statement from BOL.
Everett Wilson
ewilson10@yahoo.com
June 12, 2003 at 7:54 pm
Just another way of doing it
Select a.*, c.*
From ArchivedLeads.dbo.allAgedLeads a, applicant c
Where Exists(Select alp.leadSourceId
From AgedLeadPlanMatchedLead alp
Where (alp.leadSourceId = @allAgedLeads AND
alp.applicantLeadId = a.applicantLeadid) AND
(alp.leadSourceId = @applicant AND
alp.applicantLeadId = c.applicantLeadid))
Order By c.ApplicantName
MW
Edited by - mworku on 06/12/2003 7:54:44 PM
MW
June 12, 2003 at 11:45 pm
ewilson10: Thanks. I guess I just needed the nudge. I ran some initial tests using the order by after the second select with no luck so I was looking for an alternative. After you posting I went back to see if indeed I was missing something and got it to work. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply