quote:

---

Originally posted by visakh16

They do not want to show the mailing address if it is the same as the primary or if they have a primary transient. How do I do a select statement to check if the mailing is the same as the other 2 and if it is, do bring the mailing data back


these two statements are contradicting. one says they do not want mailing if its same as primary or primary transient and next statement says just opposite. Can you clarify what exactly are you looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

SELECT other columns..., 
NULLIF(MailingAddress,COALESCE(Primary,PrimaryTransient)) AS MailingAddress
FROM Table

quote:

---

Originally posted by visakh16

just do like

SELECT other columns..., 
NULLIF(MailingAddress,COALESCE(Primary,PrimaryTransient)) AS MailingAddress
FROM Table

This is the best we can suggest without knowing your table structure or column names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by helixpoint

Not understanding this. Here is a select sql and the date that returns. Notice that the Primary and Mailing is the same
SELECT Street, Type
FROM dbo.OffenderAddress
WHERE (OffenderID = 2436) AND (IsCurrent = 1)

550 e. madison street Primary
550 e. madison street Mailing
234 S. vine Alternate

quote:

---

Originally posted by visakh16

just do like

SELECT other columns..., 
NULLIF(MailingAddress,COALESCE(Primary,PrimaryTransient)) AS MailingAddress
FROM Table

This is the best we can suggest without knowing your table structure or column names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

Dave
Helixpoint Web Development
http://www.helixpoint.com

---

SELECT
    CASE    
        WHEN 
            (
                AddressMailing.Street = AddressPrimary.Street 
                OR AddressPrimaryTransient.Street IS NOT NULL
            )
            THEN ''
        ELSE AddressMailing.Street
    END AS Street,
    Type
FROM 
    dbo.OffenderAddress AS AddressMailing
LEFT OUTER JOIN
    dbo.OffenderAddress AS AddressPrimary 
    ON AddressMailing.OffenderID = AddressPrimary.OffenderID
    AND AddressPrimary.IsCurrent = 1
    AND AddressPrimary.Type = 'Primary'
LEFT OUTER JOIN
    dbo.OffenderAddress AS AddressPrimaryTransient 
    ON AddressPrimary.OffenderID = AddressPrimaryTransient.OffenderID
    AND AddressPrimaryTransient.IsCurrent = 1
    AND AddressPrimaryTransient.Type = 'Primary Transient'           
WHERE 
    AddressMailing.OffenderID = 2436 
    AND AddressMailing.IsCurrent = 1
    AND AddressMailing.Type = 'Mailing'

quote:

---

Originally posted by jhatley247

That would still only ever bring one address back. It sounds as though he would like the mailing and primary if they differ and only the primary transient if there is one. Removing this "AND [MailingAddress] = [PrimaryTransientAddress]" from the second subselect in the first query I posted yesterday would do this.


Spartan

---

-- Create the table with test data
SELECT * INTO Testtbl
FROM
(
SELECT 2436 AS OffenderID,'550 e. madison street' AS Street, 	'Primary' AS [Type] UNION ALL
SELECT 2436, '550 e. madison street', 'Mailing' UNION ALL
SELECT 2436,'234 S. vine', 'Alternate' UNION ALL
SELECT 1110,'112 Seventh Street', 'Primary' UNION ALL
SELECT 1110,'112 Seventh Street', 'Mailing' UNION ALL
SELECT 1110,'2/365 hayman street', 'Alternate' UNION ALL
SELECT 2343,'', 'Primary' UNION ALL
SELECT 2343,'1134 Kirgiston street', 'Mailing' UNION ALL
SELECT 2343,'1123 Vahmann Street', 'Alternate' 
)t

SELECT OffenderID,Street, Type
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY OffenderID,Street ORDER BY CASE WHEN Type='Primary' THEN 1 WHEN  Type='Primary Transient' THEN 2  WHEN  Type='Mailing' THEN 3 END ASC) AS Rn,
OffenderID,
Street, 
[Type]
FROM Testtbl

)t
WHERE Rn=1


output
--------------------------------
OffenderID	Street	                Type
1110	        112 Seventh Street	Primary
1110	        2/365 hayman street	Alternate
2343		                        Primary
2343	        1123 Vahmann Street	Alternate
2343	        1134 Kirgiston street	Mailing
2436	        234 S. vine	        Alternate
2436	        550 e. madison street	Primary