outputting contents of nested query in output

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)

outputting contents of nested query in output

Author

Topic

pcooke
Starting Member

2 Posts

Posted - 2008-07-23 : 20:04:07

I have been googling for a while. Hoping someone knows the answer to my delema. I need the contents of the RED sub query to be returned as a column in the outer select clause. Any on how I can concatinate the sub query into a sigle string seperated by spaces and returned as a value? Thanks for any help

SELECT CA.CUST_ADDR_ID,
CN.CUST_NM_NM,
CA.CUST_ADDR_TYP_CD,
CA.CUST_ADDR_STR_1,
CA.CUST_ADDR_STR_2,
CA.CUST_ADDR_CITY_NM,
CA.STT_ID,
CA.CUST_ADDR_POST_CD
FROM CUSTOMER_ADDRESS CA,
CUSTOMER_NAME CN
WHERE CA.CUST_ID = CN.CUST_ID
AND CA.CUST_ADDR_POST_CD NOT IN (SELECT PS1.POST_US_CD
FROM POSTAL_US PS1
WHERE CA.STT_ID = PS1.STT_ID
AND CA.CUST_ADDR_CITY_NM = PS1.POST_US_CITY_NM);

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-23 : 23:31:21

I think with the NOT IN, what you are asking is a logical imposibility. But if you fiddle with the code below, you may find something that meets your requirements:
SELECT POST_US_CD, CA.CUST_ADDR_ID,
CN.CUST_NM_NM,
CA.CUST_ADDR_TYP_CD,
CA.CUST_ADDR_STR_1,
CA.CUST_ADDR_STR_2,
CA.CUST_ADDR_CITY_NM,
CA.STT_ID,
CA.CUST_ADDR_POST_CD
FROM
(SELECT PS1.POST_US_CD
FROM POSTAL_US) PS1
LEFT JOIN
CUSTOMER_ADDRESS CA ON
CA.STT_ID = PS1.STT_ID
AND CA.CUST_ADDR_CITY_NM = PS1.POST_US_CITY_NM
LEFT JOIN
CUSTOMER_NAME CN
ON CA.CUST_ID = CN.CUST_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 04:18:18

if the above does not give you what you want, you could probably show us what you desire with some sample data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:18:21

Huh?

You want to check if there is no relation, and if there is none, show the information?

E 12°55'05.25"
N 56°04'39.16"

pcooke
Starting Member

2 Posts

Posted - 2008-07-24 : 10:50:24

I have 2 tables customer_address and postal_us

customer_address has city, state, zip that someone has entered.

postal_us has city, state, zip entries that taken from the postal service and verified for correctness.

My query finds all customer addresses that don't have a zip code that matches what the post office says is the postal code.

I want to say for this customer address with invalid postal codes, here are the proper postal codes for this city state combination.

so the output would look like
addr_ln1 addr_ln2 city state invalid_zip possible_valid_zip
..... '1 first st' 'suite 2' 'Macon' 'ga' '45678' '31201 31202'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 10:56:28

[code]SELECT ca.*,pu.zip
FROM customer_address ca
INNER JOIN postal_us pu
ON pu. city=ca.city
and pu.state=ca.state
WHERE pu.zip<>ca.zip[/code]

nirajkeshri2004
Starting Member

2 Posts

Posted - 2008-07-24 : 11:43:11

I think this modified version of your query can work. But the contraint is that user need to enter correct City and State valus in address table

SELECT CA.CUST_ADDR_ID,
CN.CUST_NM_NM,
CA.CUST_ADDR_TYP_CD,
CA.CUST_ADDR_STR_1,
CA.CUST_ADDR_STR_2,
CA.CUST_ADDR_CITY_NM,
CA.STT_ID,
CA.CUST_ADDR_POST_CD As InvalidZip
ps1.POST_US_CD As PossibleValidZip
FROM CUSTOMER_ADDRESS CA INNER JOIN CUSTOMER_NAME CN
ON CA.CUST_ID = CN.CUST_ID
INNER JOIN POSTAL_US PS1 ON CA.STT_ID = PS1.STT_ID AND CA.CUST_ADDR_CITY_NM = PS1.POST_US_CITY_NM
WHERE CA.CUST_ADDR_POST_CD NOT IN
(SELECT ps2.POST_US_CD FROM POSTAL_US PS2 WHERE CA.STT_ID = PS2.STT_ID
AND CA.CUST_ADDR_CITY_NM = PS2.POST_US_CITY_NM);

Niraj
http://omniferouszone.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources