CODEWITHSUNDEEP
sqlselectjoinmultiple-tablesmultiple-select
sirjay
sirjay

Reputation: 1766

Multiple sql table select

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

Upvotes: 1

Views: 130

Answers (4)

GarethD
GarethD

Reputation: 69749

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
        );

Example on SQL Fiddle

Upvotes: 1

user3310464
user3310464

Reputation: 9

First you need to join clients with festivals through: clients/invitations then invitations/festivals

Second you need to discard clients invited to festival2

SELECT name
    FROM clients, festivals, invitations
    WHERE clients.sex = w
        AND festivals.name = festival1
        AND festivals.name != festival2
        AND clients.id = invitations.client
        AND festivals.id = invitations.festival
        AND invitations.year = 2013

Upvotes: 0

GuyB
GuyB

Reputation: 11

I would do something like this:

SELECT c.Name
   FROM clients c
    INNER JOIN invitations i 
        ON i.client = c.id 
        AND i.year = 2013
    INNER JOIN festivals f 
        ON i.festival = f.id
        AND f.name = 'festival1'
WHERE 
    c.sex = 'w'

Upvotes: 1

juergen d
juergen d

Reputation: 204746

SELECT c.name
FROM clients c
INNER JOIN invitations i ON c.id = i.client
INNER JOIN festivals f ON f.id = i.festival 
WHERE c.sex = 'w'
AND i.year = 2013
group by c.name
having sum(case when f.name='festival1' then 1 else 0 end) > 0
and sum(case when f.name='festival2' then 1 else 0 end) = 0

Upvotes: 1

Related Questions