表连接处理字符串问题
如题:
现在有一张表zone,要连接的是两张表,left join
但有条件,根据条件不同只能连接一张表,但要写成一条sql
SELECT *
FROM dbo.[zone] z LEFT OUTER JOIN
dbo.property_info pi ON LEFT(z.spare2, 1) = 'P' AND CONVERT(int,
REPLACE(z.spare2, 'P', '')) = pi.ID LEFT OUTER JOIN
community_info ci ON LEFT(z.spare2, 1) = 'C' AND CONVERT(int, REPLACE(z.spare2,
'C', '')) = ci.id LEFT OUTER JOIN
SELECT *
FROM dbo.[zone] z LEFT OUTER JOIN
dbo.property_info pi CONVERT(int,
REPLACE(z.spare2, 'P', '')) = pi.ID
where LEFT(z.spare2, 1) = 'P' union
SELECT *
FROM dbo.[zone] z LEFT OUTER JOIN
community_info ci ON LEFT(z.spare2, 1) = 'C' AND CONVERT(int, REPLACE(z.spare2, 'C', '')) = ci.id
where LEFT(z.spare2, 1) = 'C'