A headache

lancelover

QXXH QXMC
1 Fill in the post
2 Post approval
3 Fill in the receipt
4 receipt approval

Privilege-Person Correspondence Table
XH QXXH YHMC
1 2 three
2 3 photos
3 1 Li Si
4 3 Li Si
5 4 Li Si
I want to generate a query like this: list what permissions all users have, and list all permissions at the same time.
The query results are as follows:
XH QXXH QXMC YHMC has this permission
1 1 Fill in the post Zhang San false
2 2 Posting approval Zhang San true
3 3 Fill in the receipt
4 4 Received Approval Zhang San false
5 1 Fill in the post Li Si true
6 2 Post approval Li Si false
7 3 Fill in the receipt Li Si true
8 4 Received approval Li Si true

fanfan1980

create table A (QXXH int, QXMC nvarchar (10))
insert A select 1, 'Fill in a post'
union all select 2, 'Post approval'
union all select 3, 'Fill in the receipt'
union all select 4, 'Receive Approval'

create table B (XH int, QXXH int, YHMC nvarchar (10))
insert B select 1, 2, '张三'
union all select 2, 3, '张三'
union all select 3, 1, '李四'
union all select 4, 3, '李四'
union all select 5, 4, '李四'

select tmp. *, do you have this permission = case when B.XH is null then 'False' else 'True' end from
(
select * from
(select distinct YHMC from B) as B
cross join A
tmp
left join B on tmp.QXXH = B.QXXH and tmp.YHMC = B.YHMC

--result
Does YHMC QXXH QXMC have this permission
---------- ----------- ---------- ------
Li Si 1 Post True
Li Si 2 Posting approval False
Li Si 3 Received True
Li Si 4 Receive receipt approval True
Zhang San 1 Post False
Zhang San 2 Posting approval True
Zhang San 3 Receipt Fill in True
Zhang San 4 Incoming Approval False

(8 row (s) affected)

waxiaoning

select a.xh, b.qxxh, b.qxmc, a.yhmc, qxf = case when b.qxxh is null then 0 else 1 end
from xt_qxry a full join table1 on a.qxxh = b.qxxh

pguppgdn

create table tmp1 (QXXH int, QXMC nvarchar (20))
go

create table tmp2 (XH int, QXXH int, YHMC nvarchar (20))

go

select c. *, do you have permission = case when d.xh is null then 'false' else 'true' end
 from
(
 select * from tmp1 a cross join (select distinct yhmc from tmp2) b
) c

left join tmp2 d on c.qxxh = d.qxxh and c.yhmc = d.yhmc

lancelover

Well, thank youfanfan1980

3972500

SELECT B.YHMC, A.QXXH, A.QXMC, 'TRUE' AS QX
FROM T_QX A, T_QX2 B
WHERE B.YHMC = '张三' AND A.QXXH = B.QXXH
UNION
SELECT distinct B.YHMC, A.QXXH, A.QXMC, 'FALSE' AS QX
FROM T_QX A, T_QX2 B where B.YHMC = '张三' AND
 a.QXXH NOT IN
(SELECT QXXH FROM T_QX2 WHERE YHMC = '张三')