There are tables with clients and festivals. Every festival happens every year (like New Year). But not every client is invited on any festival.
I need to get woman clients, that were invited on festival1 at this moment, but weren't invited on festival2.
Table "clients"
+-----+--------------+-----------+---------+-----+
| id | name | email | adress | sex |
+-----+--------------+-----------+---------+-----+
| 1 | Ivan | [email protected] | NY city | m |
| 2 | Michael | [email protected] | LA | m |
| 3 | Anna | [email protected] | LA | w |
| ...
+-----+--------------+-----------+---------+-----+
Table festivals
+-----+------------+-------+
| id | name | date |
+-----+------------+-------+
| 1 | festival1 | 8-03 |
| 2 | festival2 | 23-02 |
| 3 | festival3 | 1-01 |
| ...
+-----+------------+-------+
Talbe "invitations"
+--------+----------+------+
| client | festival | year |
+--------+----------+------+
| 1 | 2 | 2013 |
| 3 | 1 | 2009 |
| ...
+--------+----------+
I started to do something like this query, but it need to be corrected:
SELECT name
FROM clients, festivals, invitations
WHERE clients.sex = w
AND festivals.name = festival1
AND clients.id = invitations.client
AND invitations.year = 2013
You can use NOT EXISTS to eliminate results from your query:
SELECT *
FROM Clients
INNER JOIN Invitations
ON Invitations.Client = Clients.ID
INNER JOIN Festivals
ON Festivals.ID = Invitations.Festival
WHERE Festivals.Name = 'Festival1'
AND Clients.Sex = 'W'
AND Invitations.Year = 2013
AND NOT EXISTS
( SELECT 1
FROM Invitations i2
INNER JOIN Festivals f2
ON f2.ID = i2.Festival
WHERE i2.Client = Clients.ID
AND f2.Name = 'Festival2'
AND i2.Year = Invitations.Year
);