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

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.


LeadCoAsked:
Who is Participating?
 
BrandonGalderisiCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
SharathData EngineerCommented:
can you provide some sample set and your desired output?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.