MySql_Select.sql
6.43 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
/*
select
losp_BasisElement_categorylist.BECategoryListID, categorynamelist.BECategoryID as CatID, categorynamelist.BECategoryName as CatName, groups.BECategoryID as SubCatID, groups.BECategoryName as SubCatName
from losp_BasisElement
inner join losp_BasisElement_categorylist on losp_BasisElement.BECategoryListID=losp_BasisElement_categorylist.BECategoryListID
inner join losp_BasisElement_categorynamelist as categorynamelist on losp_BasisElement_categorylist.BECategoryID=categorynamelist.BECategoryID
left join losp_BasisElement_categorynamelist as groups on categorynamelist.BECategoryID=groups.BECategoryGroupID
where ID=14;
*/
-- select * from losp_BasisClipboard as BC
-- inner join losp_BasisClipboard_objvar as BCo on BC.PartOfObjVarID=BCo.PartOfObjVarID
-- left join losp_BasisClipboard_category as BCc on BC.CategoryID=BCc.CategoryID
--
-- where
-- PartOfID=6
-- and
-- PartOfObjVar='TopicBC' and CategoryName=''
--
--
-- ;
--
-- select * from losp_BasisClipboard
-- inner join losp_BasisElement_profilecollection_writeauthorisation on ProfileCollectionID=PartOfID
--
-- where
-- PartOfObjVarID=(select PartOfObjVarID from losp_BasisClipboard_objvar where PartOfObjVar='MemberProfileBC' )
-- and
-- PCListID=1
-- and
-- AttachID=14 ;
--
-- select * from losp_BasisElement_profilecollection_writeauthorisation inner join losp_BasisClipboard
-- on ProfileCollectionID=PartOfID
-- where
-- PartOfObjVarID=(select PartOfObjVarID from losp_BasisClipboard_objvar where PartOfObjVar='MemberProfileBC' )
-- and
-- PCListID=8 ;
select ID, PartOfID, AttachID, AttachStateID from losp_BasisElement
inner join losp_BasisElement_profilecollection_writeauthorisation on PCAuthWriteListID=PCListID
inner join losp_BasisClipboard on ProfileCollectionID=PartOfID
where
PartOfObjVarID=(select PartOfObjVarID from losp_BasisClipboard_objvar where PartOfObjVar='MemberProfileBC' )
and
ID=1568 ;
select * from losp_BasisElement
where
ID=1568 and
((select AttachID from losp_BasisElement_profilecollection_readauthorisation
inner join losp_BasisClipboard on ProfileCollectionID=PartOfID
where PartOfObjVarID=(select PartOfObjVarID from losp_BasisClipboard_objvar where PartOfObjVar='MemberProfileBC' )
and AttachID=14 and PCAuthReadListID=PCListID limit 1
)
)
limit 10 ;
SELECT PartOfID, MTextID, Name, AuthReadListID, AuthWriteListID, PCAuthReadListID, PCAuthWriteListID
-- 'TopicBE' as losp_classname, LinkerProfileID, Name, PartOfID, AttachID, AttachStateID, StateID, State, PartOfObjVar, ClassID, ID, MTextID, Author_ProfileID, LanguageCode, Subject, Text, CreateDate, TagListID, BECategoryChoiceBEID, BECategoryListID, AuthReadListID, AuthWriteListID, PCAuthReadListID, PCAuthWriteListID, BasisElementDesignID
FROM losp_BasisClipboard
INNER JOIN losp_BasisClipboard_state on losp_BasisClipboard_state.StateID = losp_BasisClipboard.AttachStateID
INNER JOIN losp_BasisClipboard_objvar on losp_BasisClipboard_objvar.PartOfObjVarID = losp_BasisClipboard.PartOfObjVarID
inner join losp_BE_topic on losp_BE_topic.MTextID = losp_BasisClipboard.AttachID
inner join losp_BasisElement on losp_BasisElement.ID=losp_BE_topic.MTextID
inner join losp_BasisElement_readauthorisation on losp_BasisElement.AuthReadListID=losp_BasisElement_readauthorisation.ListID
where
( losp_BasisClipboard.PartOfID = 6 AND losp_BasisClipboard_objvar.PartOfObjVar='TopicBC')
&&
(
(
losp_BasisElement.ID!=0
AND
(
(losp_BasisElement.AuthReadListID is NULL OR losp_BasisElement.AuthReadListID=0)
OR
( losp_BasisElement_readauthorisation.ProfileID= 16
OR losp_BasisElement_readauthorisation.ProfileID= 1473
OR losp_BasisElement_readauthorisation.ProfileID= 1225
)
)
OR
(
PCAuthReadListID!=0
AND
(select losp_PMMemberBC.AttachID from losp_BasisElement_profilecollection_readauthorisation
inner join losp_BasisClipboard as losp_PMMemberBC on ProfileCollectionID=losp_PMMemberBC.PartOfID
where
losp_PMMemberBC.PartOfObjVarID=27 and PCAuthReadListID=PCListID
AND losp_PMMemberBC.AttachID=16
OR losp_PMMemberBC.AttachID=1473
OR losp_PMMemberBC.AttachID=1225 limit 1)
)
)
) ORDER BY Name ASC LIMIT 10;
-- select * from losp_BasisElement_profilecollection_readauthorisation
-- inner join losp_BasisClipboard as losp_PMMemberBC on ProfileCollectionID=losp_PMMemberBC.PartOfID
-- where
-- losp_PMMemberBC.PartOfObjVarID=27 and 0=PCListID
-- AND losp_PMMemberBC.AttachID=16
-- OR losp_PMMemberBC.AttachID=1473
-- OR losp_PMMemberBC.AttachID=1225 limit 1
--;
SELECT PartOfID, MTextID, Name, AuthReadListID, AuthWriteListID, PCAuthReadListID, PCAuthWriteListID
-- 'TopicBE' as losp_classname, LinkerProfileID, Name, PartOfID, AttachID, AttachStateID, StateID, State, PartOfObjVar, ClassID, ID, MTextID, Author_ProfileID, LanguageCode, Subject, Text, CreateDate, TagListID, BECategoryChoiceBEID, BECategoryListID, AuthReadListID, AuthWriteListID, PCAuthReadListID, PCAuthWriteListID, BasisElementDesignID
FROM losp_BasisClipboard
INNER JOIN losp_BasisClipboard_state on losp_BasisClipboard_state.StateID = losp_BasisClipboard.AttachStateID
INNER JOIN losp_BasisClipboard_objvar on losp_BasisClipboard_objvar.PartOfObjVarID = losp_BasisClipboard.PartOfObjVarID
inner join losp_BE_topic on losp_BE_topic.MTextID = losp_BasisClipboard.AttachID
inner join losp_BasisElement on losp_BasisElement.ID=losp_BE_topic.MTextID
inner join losp_BasisElement_readauthorisation on losp_BasisElement.AuthReadListID=losp_BasisElement_readauthorisation.ListID
where
( losp_BasisClipboard.PartOfID = 6 AND losp_BasisClipboard_objvar.PartOfObjVar= 'TopicBC')
&&
(
(losp_BasisElement.ID!=0 AND
(
(losp_BasisElement.AuthReadListID is NULL OR losp_BasisElement.AuthReadListID=0)
OR
( losp_BasisElement_readauthorisation.ProfileID= 15 OR losp_BasisElement_readauthorisation.ProfileID= 1473 )
)
OR
(
PCAuthReadListID!=0
AND
( select losp_PMMemberBC.AttachID from losp_BasisElement_profilecollection_readauthorisation inner join losp_BasisClipboard as losp_PMMemberBC on ProfileCollectionID=losp_PMMemberBC.PartOfID
where losp_PMMemberBC.PartOfObjVarID=27 and PCAuthReadListID=PCListID AND losp_PMMemberBC.AttachID= 15 OR losp_PMMemberBC.AttachID=1473 limit 1 )) ) ) ORDER BY Name ASC LIMIT 10
;