Solved

How do you write a query when 2 fields in two different tables need to be joined to sort by Example..?

Posted on 2008-10-15
3
191 Views
Last Modified: 2010-03-20
I want to  write this query :

select  A.lead, A.date_sold,A.price,A.money,B.pay_amount,B.note_date
from TableA  A, TableB  B
where customerid =  1  (on both tables)

I need the info above but I need to sort the data by combining the A.date_sold and the B.note_date together so the rows are sorted by date

Another way of explaining it:

Select * from table A   where customerid = 1 (the date field in this table is date_sold)
Select * from table B where customerid = 1 (the date field in this table is note_date)

I need them sorted together so the queries are in order by date.


0
Comment
Question by:LeadCo
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22725888
this will do:
select  A.lead, A.date_sold,A.price,A.money,B.pay_amount,B.note_date 
from TableA  A
join TableB  B
  on b.Customerid = a.CustomerID
where a.customerid =  1 

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22725967
Do you need to sort by the newer of the two values (note_date and date_sold)?

If so?

"I need the info above but I need to sort the data by combining the A.date_sold and the B.note_date together so the rows are sorted by date "
You didn't say which takes precedence as the primary sort.  Or is by the newer of the two?

(select  A.lead, A.date_sold,A.price,A.money,B.pay_amount,B.note_date, case when a.date_sold > b.note_date then a.date_sold else b.note_date end as SortDate
from TableA  A
join TableB  B
  on b.Customerid = a.CustomerID
where a.customerid =  1 )
order by case when a.date_sold > b.note_date then a.date_sold else b.note_date end

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 22915479
can you provide some sample set and your desired output?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question