Please help with SELECT sequel statement.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please help with SELECT sequel statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-03-03 : 18:21:02

Given LoanNum as a input parameter and need to return all the same addresses. I know this table is a mess but I have been
asked to do not to clean up the data. Any helps would greatly appreciated. Please see the desired result below.

--SQL2005

-- TRUNCATE TABLE T
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('100882', '1146 EAST 32ND STREET', 'LOS ANGELES', 'CA', '90011')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('103385', '1146 EAST 32ND STREET', 'LOS ANGELES', 'CA', '90011')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('106889', '11738 TEMPEST HARBOR LOOP ', 'VENICE', 'FL', '34292')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('100001', '11738 Tempest Harbor Loop', 'Venice', 'FL', '34292')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('110131', '11738 Tempest Harbor Loop', 'Venice', 'FL', '34292-3819')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('109409', '12714 RIVER RD', 'Fort Myers', 'FL', '33905-1771')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('109410', '12722 RIVER RD', 'Fort Myers', 'FL', '33905-1771')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('109411', '12780 RIVER ROAD', 'Fort Myers', 'FL', '33905-1771')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('103107', '2661 REDLANDS AVENUE', 'PERRIS', 'CA', '92571')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('102545', '5955 E GOLF CLUB CT', 'CORNVILLE', 'AZ', '86325')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('102546', '5955 E GOLF CLUB CT', 'CORNVILLE', 'AZ', '86325')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('103019', '6031 FUJI STREET', 'CORONA', 'CA', '92880')
INSERT INTO dbo.t([LoanNum],[Address1],[PropertyCity],[PropertyState],[PropertyZip])VALUES ('101013', '6031 FUJI STREET', 'CORONA', 'CA', '92880')
go

SELECT *
FROM T
ORDER BY Address1 ASC
GO

LoanNum Address1 PropertyCity PropertyState PropertyZip
---------- ------------------------------ -------------------- ------------- -----------
100882 1146 EAST 32ND STREET LOS ANGELES CA 90011
103385 1146 EAST 32ND STREET LOS ANGELES CA 90011
106889 11738 TEMPEST HARBOR LOOP VENICE FL 34292
100001 11738 Tempest Harbor Loop Venice FL 34292
110131 11738 Tempest Harbor Loop Venice FL 34292-3819
109409 12714 RIVER RD Fort Myers FL 33905-1771
109410 12722 RIVER RD Fort Myers FL 33905-1771
109411 12780 RIVER ROAD Fort Myers FL 33905-1771
103107 2661 REDLANDS AVENUE PERRIS CA 92571
102545 5955 E GOLF CLUB CT CORNVILLE AZ 86325
102546 5955 E GOLF CLUB CT CORNVILLE AZ 86325
103019 6031 FUJI STREET CORONA CA 92880
101013 6031 FUJI STREET CORONA CA 92880

-- If pass in @LoanNum = '100882' THEN return all the same addresses.
= '106889'

SELECT *
FROM T
WHERE LoanNum = @LoanNum

-- Desired resulted:
LoanNum Address1 PropertyCity PropertyState PropertyZip
---------- ------------------------------ -------------------- ------------- -----------
100882 1146 EAST 32ND STREET LOS ANGELES CA 90011
103385 1146 EAST 32ND STREET LOS ANGELES CA 90011

106889 11738 TEMPEST HARBOR LOOP VENICE FL 34292
100001 11738 Tempest Harbor Loop Venice FL 34292
110131 11738 Tempest Harbor Loop Venice FL 34292-3819

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-03 : 18:45:47

[code]SELECT *
FROM T
WHERE LoanNum = @LoanNum
UNION ALL
SELECT *
FROM T
WHERE Address1 =
(SELECT Address1
FROM T
WHERE LoanNum = @LoanNum)
ORDER BY Address1 ASC;
[/code]

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-03-03 : 18:57:57

Thank you. I will test it out and let you know.

quote:

Originally posted by ms65g

SELECT *
  FROM T
 WHERE LoanNum = @LoanNum
 UNION ALL
SELECT *
  FROM T
 WHERE Address1 = 
       (SELECT Address1 
          FROM T 
         WHERE LoanNum = @LoanNum)
 ORDER BY Address1 ASC;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources