CODEWITHSUNDEEP
mysqlsqlselect
chris.cavage
chris.cavage

Reputation: 881

Mysql Query: WHERE NOT IN

This query should give me appointments that are cancelled (post_appt_status_id = 3) and haven't been rebooked. 

SELECT appointments.id, concat_ws(' ', contacts.first_name, contacts.last_name) AS contact_name, contacts.id, appointments.start AS appt_date
 FROM
 appointments appts2
 LEFT JOIN contacts ON contacts.id = appointments.contact_id
 WHERE marketing_event_id ='740' AND appointments.company_id = '1' 
 AND appointments.post_appt_status_id = '3'
 AND contacts.id NOT IN (SELECT contact_id FROM appointments WHERE appointments.start > appt_date)
 GROUP BY contacts.id

The problem is in the subselect. I cannot pass in the appt_date value to make it work. I get an error that it's an unknown column.

Upvotes: 1

Views: 52

Answers (2)

Danilo Bustos
Danilo Bustos

Reputation: 1093

You must use the alias appts2:

SELECT appointments.id, concat_ws(' ', contacts.first_name, contacts.last_name) AS contact_name, contacts.id, appointments.start AS appt_date
 FROM
 appointments appts2
 LEFT JOIN contacts ON contacts.id = appts2.contact_id
 WHERE marketing_event_id ='740' AND appts2.company_id = '1' 
 AND appts2.post_appt_status_id = '3'
 AND contacts.id NOT IN (SELECT contact_id FROM appointments WHERE appointments.start > appts2.appt_date)
 GROUP BY contacts.id

Upvotes: 1

Mureinik
Mureinik

Reputation: 312259

As you've seen, you can't use aliases like this. Instead, you could fully qualify the column name and refer to the table from the outer query:

SELECT     appointments.id, 
           concat_ws(' ', contacts.first_name, contacts.last_name) AS 
               contact_name,
           contacts.id, 
           appointments.start AS appt_date
 FROM      appointments appts2
 LEFT JOIN contacts ON contacts.id = appointments.contact_id
 WHERE     marketing_event_id ='740' AND 
           appointments.company_id = '1' AND 
           appointments.post_appt_status_id = '3' AND
           contacts.id NOT IN (SELECT contact_id 
                               FROM   appointments
                               WHERE  appointments.start > appts2.start -- Here!
                              )
 GROUP BY contacts.id

Upvotes: 1

Related Questions