sql question - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

sql question

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2011-08-24 : 09:00:53

Some body can help me to figure out how to get address, city, and state if that exist in one of table. If it exist in both table, then take one ....

for example: account 123 has address, city, and state listed in both tables, but account 456 only listed address, city, and state in one table, but account 789 does not show address, city and state in neither tables.

I tried below statement but it showed twice for 123...but I only wanted one row listed.....

SELECT x.account, X.NAME1
, CASE WHEN (H.STREET_ADDRESS + H.CITY_STATE) <> '' THEN (H.STREET_ADDRESS + H.CITY_STATE)
ELSE RTRIM(F.ADDRESS_1 + F.CITY + F.STATE + ',' + (LEFT(F.ZIP_CODE,5)))
END AS 'ADDRESS'
from (
select account, name from table1
union
select account, name from table2
)x
left join (select * from table3)h on h.account=x.account
left join (select * from table4)f on f.account=x.account
where x.account='123'

Thanks,

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-24 : 11:07:30

you might look into row_number() partition. if you could provide sample data we could give you a more definite answer

If you don't have the passion to help people, you have no passion

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-08-24 : 15:26:18

Not sure I understand your data model, but...

Do you know isNull function and how to use it with outer joins?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2011-08-24 : 17:04:42

[code]
CREATE table #table1 (account varchar(100), NAME1 varchar(100), STREET_ADDRESS varchar(100))
CREATE TABLE #table2 (account varchar(100), NAME1 varchar(100), ADDRESS_1 varchar(100))

INSERT INTO #table1
SELECT '123','aaa','123 aaa1'
UNION
SELECT '456','bbb','456 bbb'
UNION
SELECT '789','ccc',''

INSERT INTO #table2
SELECT '123','aaa','123 aaa2'
UNION
SELECT '456','bbb',''
UNION
SELECT '789','ccc',''

SELECT t1.account, t1.NAME1,
CASE WHEN t1.STREET_ADDRESS <> '' THEN t1.STREET_ADDRESS
ELSE t2.ADDRESS_1 END AS [ADDRESS]
from #table1 t1
LEFT JOIN #table2 t2 ON t1.account = t2.account

[/code]

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources