2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>:
> On 2015-09-04 9:40 AM, Richard Reina wrote: > >> I have the following two tables; >> >> mysql> select * from challenge; >> +----+--------+-------------+--------+------+---------+--------------+ >> | ID | PLR_ID | ACC_TYPE_ID | season | year | CHAR_ID | >> +----+--------+-------------+--------+------+---------+--------------+ >> | 1 | 1 | 1 | Fall | 2015 | >> 1175 >> | >> | 2 | 1 | 4 | Fall | 2015 | >> 1175 >> | >> | 3 | 1 | 3 | Fall | 2015 | >> 1175 >> | >> | 4 | 1 | 10 | Fall | 2015 | >> 1175 | >> | 5 | 1 | 13 | Fall | 2015 | >> 1175 | >> | 6 | 1 | 2 | Fall | 2015 | >> 1175 >> | >> +----+----------+----------------------+--------+-------+-------------+ >> 6 rows in set (0.00 sec) >> >> mysql> select * from patrocinio; >> +----+------------+---------------+-------------+ >> | ID | PTRN_ID | CHLNG_ID | AMOUNT | >> +----+------------+---------------+-------------+ >> | 1 | 1 | 1 | 1.00 | >> | 2 | 4 | 3 | 2.00 | >> | 3 | 3 | 6 | 1.00 | >> +----+-----------+-----------------+------------+ >> I would like to select all rows from challenges which are NOT linked to a >> patrocinio with the PTRN_ID -- which would be rows 2 through 6 of >> challenges. >> >> I am trying to go about this with a LEFT JOIN query but it does not seem >> to >> be working for me. >> >> mysql> select c.ID FROM challenge c LEFT JOIN patrocinio p ON >> c.ID=p.CHLNG_ID WHERE p.PTRN_ID!=1; >> > > ... where p.chlng_id IS NULL; > > (Look up exclusion joins) > > PB > > ----- > > > Empty set (0.00 sec) >> >> Instead of getting rows 2 through 6 of challenges I get no rows. >> >> Any help on how to correctly do this query would be greatly appreciated. >> >> Thanks >> >> > Got it to work this way: SELECT c.ID AS ID FROM challenge c WHERE c.ID NOT IN ( SELECT ID from patrocinio p WHERE p.PTRN_ID=1 );
