CODEWITHSUNDEEP
sqlsql-serverdistinct
James Hay
James Hay

Reputation: 7315

Select DISTINCT values with 3 Inner joins

I have a few tables that I need to link together to get a specific value, here is my current query

SELECT DISTINCT bu.Email,iv.Code,ex.ExNumber 
FROM Invoices AS iv
INNER JOIN Clients AS cs
ON iv.Code = cs.Code
INNER JOIN BusinessUser_ExNumbers AS ex
ON cs.ExNumber = ex.ExNumber 
INNER JOIN BusinessUsers AS bu
ON ex.Userid = bu.Id
WHERE iv.BatchId = '74b43669-c80f-4b44-999c-a1dfe5695844' // Test value

Basically the links to my invoices are held in the invoice table, and what I need to do is send a link to the user who is specified as being the recipient of the invoice. The code works but it's not as 'distinct' as I would like. I dont want any rows with the same email and exnumber as any other row. Unfortunately if there is more than 1 iv.Code that ends up linking to a specific user I get multiple rows. I understand why this is but I can't think of a way to make that not happen.

Thanks!

EDIT: Sorry that was really stupid of me. I didn't need to select the iv.Code and that's what was causing the issue. That's probably why it didn't make much sense. Anyway since I posted the question I have tried Martin Smiths answer below and it works, so that is the answer to the question. If you don't need the iv.Code it can just be removed.

Upvotes: 3

Views: 2207

Answers (1)

Martin Smith
Martin Smith

Reputation: 453067

The below will return the MAX code in the event that there are multiple for a particular Email, Number combination.

SELECT bu.Email,
       MAX(iv.Code) AS Code,
       ex.ExNumber
FROM   Invoices AS iv
       INNER JOIN Clients AS cs
         ON iv.Code = cs.Code
       INNER JOIN BusinessUser_ExNumbers AS ex
         ON cs.ExNumber = ex.ExNumber
       INNER JOIN BusinessUsers AS bu
         ON ex.Userid = bu.Id
WHERE  iv.BatchId = '74b43669-c80f-4b44-999c-a1dfe5695844'
GROUP  BY bu.Email,
          ex.ExNumber

Upvotes: 1

Related Questions