-
-
Notifications
You must be signed in to change notification settings - Fork 47
/
example_queries.sql
106 lines (101 loc) · 4.16 KB
/
example_queries.sql
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
--- Quests
WITH quest_relations AS (
SELECT 'accept' as source, qr.quest, creature.id as creature_id, creature.position_x, creature.position_y, creature.map
FROM creature
JOIN creature_questrelation qr ON qr.id = creature.id
UNION ALL
SELECT 'complete' as source, qr.quest, creature.id as creature_id, creature.position_x, creature.position_y, creature.map
FROM creature
JOIN creature_involvedrelation qr ON qr.id = creature.id
UNION ALL
SELECT 'progress' as source, qr.quest, creature.id as creature_id, creature.position_x, creature.position_y, creature.map
FROM creature
JOIN creature_involvedrelation qr ON qr.id = creature.id
)
SELECT
distinct
qr.source,
qr.quest,
CASE
WHEN qr.source = 'accept' THEN qt.Details
WHEN qr.source = 'progress' THEN qt.RequestItemsText
ELSE qt.OfferRewardText
END as "text",
cdie.DisplayRaceID,
cdie.DisplaySexID,
ct.name,
qr.creature_id as id
FROM
quest_relations qr
JOIN quest_template qt ON qr.quest = qt.entry
JOIN creature_template ct ON qr.creature_id = ct.entry
JOIN db_CreatureDisplayInfo cdi ON ct.display_id1 = cdi.ID
JOIN db_CreatureDisplayInfoExtra cdie ON cdi.ExtendedDisplayInfoID = cdie.ID
where
qr.map = 1
and (cdie.DisplayRaceID = 2 or cdie.DisplayRaceID = 8)
and qr.position_x > -1000 and qr.position_x < 2000
and qr.position_y > -5500 and qr.position_y < -2000
AND (
(qr.source = 'accept' AND qt.Details IS NOT NULL AND qt.Details != '')
OR (qr.source = 'progress' AND qt.RequestItemsText IS NOT NULL AND qt.RequestItemsText != '')
OR (qr.source = 'complete' AND qt.OfferRewardText IS NOT NULL AND qt.OfferRewardText != '')
);
--- gossip
select distinct
cdie.DisplayRaceID,
cdie.DisplaySexID,
creature.id,
ct.name,
IF(cdie.DisplaySexID = 0, bt.male_text, bt.female_text) AS text
from creature
join creature_template ct on creature.id = ct.entry
join gossip_menu gm on ct.gossip_menu_id = gm.entry
join npc_text nt on gm.text_id = nt.ID
join broadcast_text bt on nt.BroadcastTextID0 = bt.entry
JOIN db_CreatureDisplayInfo cdi ON ct.display_id1 = cdi.ID
JOIN db_CreatureDisplayInfoExtra cdie ON cdi.ExtendedDisplayInfoID = cdie.ID
where
creature.map = 1
and (cdie.DisplayRaceID = 2 or cdie.DisplayRaceID = 8)
and creature.position_x > -1000 and creature.position_x < 2000
and creature.position_y > -5500 and creature.position_y < -2000
AND (
(cdie.DisplaySexID = 0 AND bt.male_text IS NOT NULL AND bt.male_text != '')
OR (cdie.DisplaySexID = 1 AND bt.female_text IS NOT NULL AND bt.female_text != '')
);
--- get number of characters in quest text
WITH quest_relations AS (
SELECT 'accept' as source, qr.quest, creature.id as creature_id, creature.position_x, creature.position_y, creature.map
FROM creature
JOIN creature_questrelation qr ON qr.id = creature.id
UNION ALL
SELECT 'complete' as source, qr.quest, creature.id as creature_id, creature.position_x, creature.position_y, creature.map
FROM creature
JOIN creature_involvedrelation qr ON qr.id = creature.id
)
SELECT
sum(CHAR_LENGTH(text)) as total_characters
FROM
(
SELECT
distinct
CASE
WHEN qr.source = 'accept' THEN qt.Details
WHEN qr.source = 'progress' THEN qt.RequestItemsText
ELSE qt.OfferRewardText
END as "text"
FROM
quest_relations qr
JOIN quest_template qt ON qr.quest = qt.entry
JOIN creature_template ct ON qr.creature_id = ct.entry
JOIN db_CreatureDisplayInfo cdi ON ct.display_id1 = cdi.ID
JOIN db_CreatureDisplayInfoExtra cdie ON cdi.ExtendedDisplayInfoID = cdie.ID
where
(
(qr.source = 'accept' AND qt.Details IS NOT NULL AND qt.Details != '')
OR (qr.source = 'progress' AND qt.RequestItemsText IS NOT NULL AND qt.RequestItemsText != '')
OR (qr.source = 'complete' AND qt.OfferRewardText IS NOT NULL AND qt.OfferRewardText != '')
)
) as subquery;
--- get number of characters in gossip text