MYSQL Query Problem

[lyroy]

Ok, here is my project tables:

SELECT * FROM domaintable;

+--------+-------------------+
| url_id | url |
+--------+-------------------+
| 1 | www.sexe.com |
| 2 | www.nzmateurs.com |
| 3 | www.tits.com |
| 4 | www.applesbe.com |
+--------+-------------------+

SELECT * FROM rules;
+--------+--------+--------+
| url_id | sco_id | status |
+--------+--------+--------+
| 1 | 1 | A |
| 3 | 1 | A |
| 3 | 2 | A |
+--------+--------+--------+

A : for authorise
R : for refuse

and

SELECT * FROM school;
+--------+--------+-------------------+
| sco_id | number | shool |
+--------+--------+-------------------+
| 1 | 999 | Toutes le ecoles |
| 2 | 118 | École Le Castelet |
+--------+--------+-------------------+

What I want to do is to have the listing of the NONE AUTHORISE site for each school.

FOR Example:

domain_table contain all the RESTRICTED website. And one school (118) want to have access to www.tits.com. They post a request to rules table and add the data: url_id: 1 , sco_id: 2 and status: A.

I try this SQL SELECT (it seems to be good but it is not working.):

SELECT DISTINCT domaintable.url FROM domaintable,school,rules LEFT JOIN rules as R ON R.url_id =domaintable.url_id WHERE R.url_id IS NULL AND school.numero ='118' AND rules.statut='A';

Thanks for you help

[tomhath]

I assume you mean you want all rows in "domaintable" that are not authorized in the "rules" table?

There are three problems in your join. First, you never join the sco_id in the school and rules tables. Second, you have all three tables in an inner join (" FROM domaintable,school,rules"), so the query cannot return any rows. Third, rules.status will never be 'A' when R.url_id is null

I didn't test this but it should be closer:

SELECT DISTINCT D.url
FROM domaintable as D
LEFT JOIN (rules as R
INNER JOIN school as S
ON R.sco_id = S.sco_id AND S.numero ='118' AND R.statut='A')
ON R.url_id =D.url_id
WHERE R.url_id IS NULL ;