现在有两个表,一个用户表,一个地区表,类似这样:
create table user (
userID int ,
provinceID varchar(50) ,
cityID varchar(50)
)
create table area (
id varchar(50),
name varchar(50)
)
我希望能够一次性把中文用户信息读出来,sql像这样,得到一个笛卡尔积
select
user.userID, --用户ID
p.name as province, --省份名称
c.name as city --城市名称
from
user as u,
area as p,
area as c
where
u.provinceID = p.id and
u.cityID = c.id
可是有些用户没有cityID,以上语句就不适用了,于是这样,改为左联
select
user.userID,
p.name as province,
c.name as city
from user as u
left join area as p
on u.provinceID = p.id
left join area as c
on u.cityID = c.id
照搬改成hql,一运行,好了报错了。
Path expected for join!
搜了一圈回来,得到的结论是,要么用sql,要么在实体类上设置我没法忍的映射关系。最后想了个变通的方法,给area加了一条id为"999999"空记录,然后得到以下sql
select
user.userID, --用户ID
p.name as province, --省份名称
c.name as city --城市名称
from
user as u,
area as p,
area as c
where
u.provinceID = p.id and
(
u.cityID = c.id or
(
u.cityId is null and
c.id = '999999'
)
)
也就是说,给主表中外键为null的记录增加一条空的关联记录。