SHOW:
|
|
- or go back to the newest paste.
| 1 | DECLARE @ContactPerson TABLE ( | |
| 2 | Id INT, | |
| 3 | Name NVARCHAR(50) | |
| 4 | ) | |
| 5 | DECLARE @ContactEmail TABLE ( | |
| 6 | Id INT, | |
| 7 | PersonId INT, | |
| 8 | Email NVARCHAR(60), | |
| 9 | PreferredInd INT | |
| 10 | ) | |
| 11 | DECLARE @ContactPhone TABLE ( | |
| 12 | Id INT, | |
| 13 | PersonId INT, | |
| 14 | Phone NVARCHAR(20), | |
| 15 | PreferredInd INT | |
| 16 | ) | |
| 17 | ||
| 18 | DECLARE @ContactOrganization TABLE ( | |
| 19 | Id INT, | |
| 20 | PersonId INT, | |
| 21 | Organization NVARCHAR(60) | |
| 22 | ) | |
| 23 | ||
| 24 | INSERT INTO @ContactPerson | |
| 25 | SELECT 1, 'John Doe' | |
| 26 | ||
| 27 | INSERT INTO @ContactEmail | |
| 28 | ( Id, PersonId, Email, PreferredInd ) | |
| 29 | SELECT 1, 1, '[email protected]', 0 | |
| 30 | UNION | |
| 31 | SELECT 2, 1, '[email protected]', 1 | |
| 32 | UNION | |
| 33 | SELECT 3, 1, '[email protected]', 0 | |
| 34 | ||
| 35 | INSERT INTO @ContactPhone | |
| 36 | ( Id, PersonId, Phone, PreferredInd ) | |
| 37 | SELECT 1, 1, '888-867-5309', 1 | |
| 38 | UNION | |
| 39 | SELECT 2, 1, '305-476-5234', 0 | |
| 40 | ||
| 41 | INSERT INTO @ContactOrganization | |
| 42 | ( Id, PersonId, Organization ) | |
| 43 | SELECT 1, 1, 'US Government' UNION SELECT 2, 1, 'US Army' | |
| 44 | ||
| 45 | SELECT DISTINCT cp.Id, cp.Name, Email, Phone, x2.Organization, x2.RowNum, ph.RowNum | |
| 46 | FROM @ContactPerson cp | |
| 47 | LEFT OUTER JOIN (SELECT DISTINCT cp.Id , Email , PreferredInd, ROW_NUMBER() OVER (ORDER BY ce.PreferredInd DESC, ce.Id) RowNum | |
| 48 | - | INNER JOIN (SELECT 3 NumberCount) Numbers ON Numbers.numberCount >= Numbers.numberCount |
| 48 | + | FROM @ContactPerson cp |
| 49 | - | LEFT OUTER JOIN (SELECT DISTINCT cp.Id , Email , PreferredInd, ROW_NUMBER() OVER (ORDER BY ce.PreferredInd DESC, ce.Id) RowNum FROM @ContactPerson cp |
| 49 | + | INNER JOIN @ContactEmail ce ON cp.Id = ce.PersonId |
| 50 | - | LEFT OUTER JOIN @ContactEmail ce ON cp.Id = ce.PersonId ) em ON cp.Id = em.Id |
| 50 | + | ) em ON cp.Id = em.Id |
| 51 | - | LEFT OUTER JOIN (SELECT DISTINCT cp.Id , PreferredInd, Phone, ROW_NUMBER() OVER (ORDER BY ph.PreferredInd DESC, ph.Id) RowNum FROM @ContactPerson cp |
| 51 | + | LEFT OUTER JOIN (SELECT DISTINCT cp.Id , PreferredInd, Phone, ROW_NUMBER() OVER (ORDER BY ph.PreferredInd DESC, ph.Id) RowNum |
| 52 | - | LEFT OUTER JOIN @ContactPhone ph ON cp.Id = ph.PersonId ) ph ON cp.Id = ph.Id AND COALESCE(em.RowNum, ph.RowNum) = ph.RowNum AND COALESCE(em.PreferredInd, ph.PreferredInd) = ph.PreferredInd |
| 52 | + | FROM @ContactPerson cp |
| 53 | - | LEFT OUTER JOIN (SELECT DISTINCT co.*, ROW_NUMBER() OVER (ORDER BY co.Organization) RowNum FROM @ContactPerson cp |
| 53 | + | INNER JOIN @ContactPhone ph ON cp.Id = ph.PersonId |
| 54 | - | INNER JOIN @ContactOrganization co ON cp.Id = co.PersonId) x2 ON x2.PersonId = cp.Id AND COALESCE(em.RowNum, x2.RowNum) = x2.RowNum |
| 54 | + | ) ph ON cp.Id = ph.Id AND COALESCE(em.RowNum, ph.RowNum) = ph.RowNum AND COALESCE(em.PreferredInd, ph.PreferredInd) = ph.PreferredInd |
| 55 | LEFT OUTER JOIN (SELECT DISTINCT co.*, ROW_NUMBER() OVER (ORDER BY co.Organization) RowNum | |
| 56 | FROM @ContactPerson cp | |
| 57 | INNER JOIN @ContactOrganization co ON cp.Id = co.PersonId | |
| 58 | ) x2 ON x2.PersonId = cp.Id AND COALESCE(em.RowNum, x2.RowNum) = x2.RowNum |
