关于一个表连接查询

[Mirs]

假如我有5条数据,
1111
2222
3333
4444
其tabel 是usnitem 
用select * From usnitem where usn in('1111','2222','3333','4444') and unp='00E'
其结果是:
usn                   csn                              upn                                                          
--------------------- -------- -------------------------------
1111                5555                            00E
2222                6666                            00E
3333                7777                            00E
4444                8888                            00E

在用select * From usnitem where usn in('5555','6666','7777','8888') and unp='00C'
其结果：
usn                   csn                              upn                                                          
--------------------- -------- -------------------------------
5555                AAAA                            00C
6666                BBBB                             00C
7777                CCCC                            00C
8888                DDDD                           00C

怎样一次性查出结果为：

usn                   csn                              csn                                                          
--------------------- -------- -------------------------------
1111               5555                            AAAA
2222               6666                            BBBB
3333               7777                            CCCC
4444               8888                            DDDD

[fw0124]

select usnitem.usn, usnitem.csn, b.csn From usnitem a, usnitem b
  where a.usn in('1111','2222','3333','4444') and a.unp='00E'
    and a.csn = b.usn;

[fw0124]

掉了一个条件，

select usnitem.usn, usnitem.csn, b.csn From usnitem a, usnitem b
  where a.usn in('1111','2222','3333','4444') and a.unp='00E'
    and a.csn = b.usn and b.unp='00C';