SQL question - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SQL question

SQL question# Database - 数据库

m*r2006-10-23 07:10

1 楼

Table A (org_zip, dest_zip), Table zip(zipcode, city, state)
How to get
orig_city, orig_state, dest_city, dest_state
thanks,

f*g2006-10-23 07:10

2 楼

起码应该可以这么干(SQL SERVER)
SELECT org.city, org.state, dest.city, dest.state
FROM
(
(SELECT org_zip, dest_zip, city, state FROM table_A, table_zip
WHERE org_zip = zipcode AS org)
UNION
(SELECT org_zip, dest_zip, city, state FROM table_A, table_zip
WHERE dest_zip = zipcode AS dest)
)
WHERE org.org_zip = dest.org_zip AND org.dest_zip = dest.dest_zip

【在 m***r 的大作中提到】

: Table A (org_zip, dest_zip), Table zip(zipcode, city, state)
: How to get
: orig_city, orig_state, dest_city, dest_state
: thanks,

c*e2006-10-23 07:10

3 楼

try this:
select b.city ,
b.state ,
c.city ,
c.state
from table_a a ,
table_b b ,
table_b c
where a.org_zip = b.zipcode
and a.dest_zip = c.zipcode

【在 m***r 的大作中提到】

: Table A (org_zip, dest_zip), Table zip(zipcode, city, state)
: How to get
: orig_city, orig_state, dest_city, dest_state
: thanks,

y*w2006-10-23 07:10

4 楼

select a.org_zip, ( select state from b where a.org_zip = b.zipcode) as Orig
_Sate, a.dst_zip (select state from b where a.dst_zip = b.zipcode) as Dst_
Sate from a
same as the last answer. just another way. tested on DB2 UDB.