-2

I want to write a query to display all information about the Customer who made the highest transaction. It is expected that the newly created amount due column be used to identify the highest transaction

Top Table is my Customer table and the bottom table is the Transaction table

enter image description here

The highest transaction was made by Ken but I have to write a query that shows his entire row from the customer table and nothing else

GMB
  • 216,147
  • 25
  • 84
  • 135
HektikSwift
  • 3
  • 2

2 Answers2

0

I would recommend order by and top:

select c.*, t.*
from customer c
inner join (select top (1) with ties * from transaction t order by amount_due desc) t 
    on t.customerid = c.customerid

The subquery selects the transaction with the greatest amount_due (if there are ties, they are retained). We can then join this with the customer table.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Msg 4104, Level 16, State 1, Line 152 The multi-part identifier "Transaction.CustomerID" could not be bound. Msg 209, Level 16, State 1, Line 149 Ambiguous column name 'CustomerID'. Msg 207, Level 16, State 1, Line 149 Invalid column name 'Addrass'. – HektikSwift Dec 26 '20 at 11:12
  • @HektikSwift: these column names come from the image that you have shown. If there are not your actual column names, then you would need to change them... – GMB Dec 26 '20 at 11:14
  • The image I have shown are screenshots from my actual tables so the columns names are as it appears on the images. – HektikSwift Dec 26 '20 at 11:25
-1

I think this will work for you :

select c.CustomerID, c.FirstName, c.LastName, c.Addrass, c.PostCode, c.Email, c.DOB
from CustomerTable as c, TransactionTable as t
where c.CustomerID = t.CustomerID
AND Amount_Due = (select Max(Amount_Due) from TransactionTable)
AirlineDog
  • 520
  • 8
  • 21
  • Got an error message: "Msg 209, Level 16, State 1, Line 149 Ambiguous column name 'CustomerID'." And when I done Customer.CustomerID, it says "Msg 4104, Level 16, State 1, Line 149 The multi-part identifier "Customer.CustomerID" could not be bound." – HektikSwift Dec 26 '20 at 11:07
  • @HektikSwift seriously though, don't use that join syntax. – Dale K Dec 28 '20 at 08:16