Selecting field based on another table and an id

[rayman0487]

First off, sorry for the crap title. What I'm trying to do is create a table to links appointments (stored in a table) to Google Calendar for multiple users. What I've got currently is an appointments Table with "appointment_id", google_calendar Table with pair_id, google_id, appointment_id. The pair_id is based on another query that is stored in an array. What I need is to select all the appointments.appointment_id that are NOT in google_calendar with a pair_id. So:

 

Appointments

1

2

3

 

google_calendar

1 - 985 - 1

1 - 523 - 2

1 - 653 - 3

2 - 478 - 1

 

What I'm trying to get is appointment_id 2 and 3 for "pair_id" 2 - make sense? I'm hoping someone can help me out, but I've gotta run so I may not get back to this tonight, but I appreciate anyone's help. TYIA!

[requinix]

It makes sense but I don't see how it's supposed to return 2 and 3: they're both in that google_calendar table. All three appointments are.

[rayman0487]

it should return 2 and 3 because pair_id for those appointments does not exists. Basically a "pair_id" is what ties a user's account to their google account. In this example, pair_id 1 and 2 both should have all 3 appointments on their calendars.

[requinix]

Ah, for pair_id=2.

 

Do an outer join and filter to rows where the second table does not have a corresponding row.

SELECT whatever fields
FROM appointments AS a
LEFT JOIN google_calendar AS gc ON a.id = gc.appointment_id
WHERE gc.pair_id IS NULL AND probably other stuff

[Barand]

Find all combinations then left join to see which are absent

SELECT DISTINCT allrecs.pair_id
FROM 
    (
    SELECT DISTINCT a.app_id as pair_id, b.app_id
    FROM appointment AS a
    CROSS JOIN appointment as b
    ) allrecs
LEFT JOIN 
google_calendar gc USING (pair_id, app_id) 
WHERE gc.pair_id IS NULL
ORDER BY pair_id;

Edited October 17, 2013 by Barand

[rayman0487]

Barand, I'm not really sure what this statement does and can not seem to get it to work.

requinix, I have tried a statement similar to what you have provided before posting.

 

I'm back to fighting with this, but having a heck of time. Does anyone else have a suggestion on getting this to work? I'm thinking some kind of join, but I'm having such a difficult time.

[Barand]

Barand, I'm not really sure what this statement does and can not seem to get it to work.

 

I used "appointment" instead of "appointments" so change the subquery to

    SELECT DISTINCT a.app_id as pair_id, b.app_id
    FROM appointments AS a
    CROSS JOIN appointments as b

As for what it does

 

 

Find all combinations then left join to see which are absent

 

Running on the data you posted gave this

mysql> SELECT DISTINCT allrecs.pair_id
    -> FROM
    ->     (
    ->     SELECT DISTINCT a.app_id as pair_id, b.app_id
    ->     FROM appointment AS a
    ->     CROSS JOIN appointment as b
    ->     ) allrecs
    -> LEFT JOIN
    -> google_calendar gc USING (pair_id, app_id)
    -> WHERE gc.pair_id IS NULL
    -> ORDER BY pair_id;
+---------+
| pair_id |
+---------+
|       2 |
|       3 |
+---------+