Hi,
i found a solution by myself
i think it's very complex but may help others
for finding solutions
Thats the query:
SELECT entries.id, entries.head, entries.date, entries.author
FROM entries
LEFT JOIN category ON entries.cat_no REGEXP
CONCAT(\\.,category.id,(\\.|$))
LEFT JOIN entgroup ON entries.id = entgroup.eid
LEFT JOIN catgroup ON category.id = catgroup.cid
LEFT JOIN mzgroup AS mze ON mze.id = entgroup.gid
LEFT JOIN mzgroup AS mzc ON mzc.id = catgroup.gid
WHERE
MATCH (head,text) AGAINST (Who's not)
(entgroup.gid IS NULL || entgroup.gid = *user-group-id*)
GROUP BY entries.id HAVING min(catgroup.gid IS NULL || catgroup.gid =
*user-group-id*)0;
This is the result if all conditions are true otherwise i get none
++---++--+
| id | head | date | author |
++---++--+
| 6 | Who's not ... | 2002-06-13 | Martin Adler |
++---++--+
the soluton for my former problem is in the HAVING clause
the stuff in the min()-function returns only 1 when the category
have the same group-id as the user or the category don't have a
group-id otherwise it returns 0 this condition is executet for every
row in the table. If there's no conflict with the user-rights in every
row should be 1 the result of the condition
The min()-function selects the smallest value, if everything is Ok
the value is 1 and i get my result if not the value is 0 and 0 0
isn't true and i don't get a result
if anyone have a questions about this query
just mail me
i'll try to help
i also thank everyone who tried to help me to find a soluton
and if anyone have a better solution please let me know
Martin
- Original Message -
From: Martin Adler [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, June 25, 2002 9:51 PM
Subject: force no results
Hi,
is it possible to force mysql in a SELECT clause depend on a
condition to give no results?
OK here is my Query
SELECT entries.id, entries.head, entries.date, entries.author,
(catgroup.gid
IS NULL || catgroup.gid = *user-group-id*) AS indi
FROM entries
LEFT JOIN category ON entries.cat_no REGEXP
CONCAT(\\.,category.id,(\\.|$))
LEFT JOIN entgroup ON entries.id = entgroup.eid
LEFT JOIN catgroup ON category.id = catgroup.cid
LEFT JOIN mzgroup AS mze ON mze.id = entgroup.gid
LEFT JOIN mzgroup AS mzc ON mzc.id = catgroup.gid
WHERE
MATCH (head,text) AGAINST (Who's not)
(entgroup.gid IS NULL || entgroup.gid = *user-group-id*);
With thies query, i try to get a entry with a fulltext search.
The entry should only be shown if the entry-group-id is NULL or
is the same as the user-group-id and
if all category-group-id's above the entry is NULL or
is the same as the user-group-id
With GROUP BY indi i'll get maximally 2 results with which i can handle
easy in PHP
but i want to try it in MySQL.
Maybe someone knows a cleaner solution
This is the result:
++---++--+--+
| id | head | date | author | indi |
++---++--+--+
| 6 | Who's not ... | 2002-06-13 | Martin Adler |1 |
| 6 | Who's not ... | 2002-06-13 | Martin Adler |0 |
| 6 | Who's not ... | 2002-06-13 | Martin Adler |1 |
++---++--+--+
Here are the used tables
Table entries
++-+-+---++--+--
---+--
--
--+
| id | uid | cat_no | head | date | author |
text
| keywd|
++-+-+---++--+--
---+--
--
--+
| 1 | 1 | 0.1.3.8 | Headline | 2002-03-12 | Martin Adler | Some
Text
| keywords |
| 2 | 2 | 0.1.2 | Mittagessen | 2002-04-03 | Martin Adler |
Fruehjahrsputz | Spring
|
| 3 | 4 | 0.1.2 | Hausboot | 2002-04-03 | Martin Adler |
Quarktasche | Summer
|
| 4 | 2 | 0.1.2.21.22 | Kongo | 2002-04-04 | Martin Adler |
Kebup
| Springer |
| 5 | 2 | 0.1.3 | Hanfkissen| 2002-04-04 | Martin Adler |
Bananen sind lecker!| Palmen Testwagen
Radiergummi |
| 6 | 2 | 0.1.2.24| Who's not ... | 2002-06-13 | Martin Adler | .in
the group technik shouldn't be able to read this |
|
++-+-+---++--+--
---+--
--
--+
* These are the entries, which are assigned to the categories
Table category
| id | cat_no | cat
