How to join tables selecting duplicates with condition
I have 2 tables
TicketHeaders TH
| ticketID | Amount |
|---|---|
| 1 | 600 |
| 2 | 900 |
| 3 | 400 |
TicketBody TB
| ticketID | SellerName | SellerType |
|---|---|---|
| 1 | Karen | Manager |
| 1 | James | Trainee |
| 2 | John | Manager |
| 3 | James | Trainee |
What I need is to get a table with TicketID – Amount – SellerName, but if I have a ticket with 2 sellers, I need to select only the manager for that particular ticket.
The output table should be:
| ticketID | Amount | SellerName |
|---|---|---|
| 1 | 600 | Karen |
| 2 | 900 | John |
| 3 | 400 | James |
If I use left join, I get duplicate amounts for ticket 1
SELECT TH.ticketID, TH.Amount, TB.SellerName
FROM TH
LEFT JOIN TB ON TH.ticketID = TB.ticketID
