求一个sql语句

[cnaaa8]

A 表

id pid name
1 0 张三
2 1 李四
3 2 王五
4 1 苏六

pid是他的夫id 如 第二条记录的父为第一条记录

B表

id cityid
1 2
2 0
3 1

需求 首先去表B中查询id等于3的数据 得到的是 id=3 cityid=1 然后去a表中查询pid等于b表中查询得到的pid的值 如这里查询pid等于1的

[rucypli]

select A.* from A,B where A.pid=B.cityid and B.id=3;

[wwwwb]

select A.* from A inner join B on A.pid=B.cityid where B.id=3;

[Rotel-刘志东]

select a.*,from A表 a,B表 b where a.pid=b.cityid and b.id=3;

[梅子]

select * from A where pid in (select cityid from B where id=3) and pid=1

[龑]

CREATE TABLE t1(
id INT PRIMARY KEY,
cityid INT,
NAME VARCHAR(100)
);

INSERT INTO t1 VALUES
(1 ,   0 ,  '张三'),
(2 ,   1 ,  '李四'),
(3 ,   2 ,  '王五'),
(4  ,  1 ,  '苏六');

CREATE TABLE t2(
id INT PRIMARY KEY,
cityid INT
);


INSERT INTO t2 VALUES
(1,    2),
(2 ,   0),
(3 ,   1);

SELECT * FROM t1 WHERE pid=(SELECT cityid FROM t2 WHERE id=3);

[WPooh]

select a.* from a left join b on a.pid=b.cityid where b.id=3

[Neptune_yin]

select pid,name from a join b on(b.cityid=a.pid) where b.id=3 ；

[dz215136304]

select a.*,from A表 a,B表 b where a.pid=b.cityid and b.id=3;