Peter, Thank you very much for your reply. Three weeks later I am realizing that the 'NOT IN' solution I had above does not work after all. Accordingly, I went back and looked at your solution and it is close but it only gives NULL results I am looking for something that excludes challenges that have are linked to p.PTRN_ID=1. So p.PTRN_ID!=1. Thanks nonetheless for trying.
This may be unsolvable. 2015-09-04 12:30 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net>: > On 2015-09-04 11:39 AM, Richard Reina wrote: > > > 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 >> >> ----- >> >> > > Hi Peter, > > Thanks for the reply. Along those lines I have also tried: > > select c.ID FROM challenge c LEFT JOIN ( SELECT p.ID FROM patrocinio WHERE > p.PTRN_ID=1 ) p ON p.CHLG_ID=c.ID WHERE p.PTRN_ID IS NULL; > > But that's not working either. > > > drop table if exists patrocinio, challenge; > create table challenge( > id smallint,plr_id smallint,acc_type_id smallint, > season char(4), year year, char_id smallint ); > insert into challenge values > ( 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); > create table patrocinio( > id smallint, ptrn_id smallint, chlng_id smallint, amount decimal(6,2) ); > insert into patrocinio values > ( 1 , 1 , 1 , 1.00 ), > ( 2 , 4 , 3 , 2.00 ), > ( 3 , 3 , 6 , 1.00 ); > > select c.id , p.chlng_id > from challenge c > left join patrocinio p on c.id=p.chlng_id ; > +------+----------+ > | id | chlng_id | > +------+----------+ > | 1 | 1 | > | 3 | 3 | > | 6 | 6 | > | 2 | NULL | > | 4 | NULL | > | 5 | NULL | > +------+----------+ > > select c.id , p.chlng_id > from challenge c > left join patrocinio p on c.id=p.chlng_id > where p.chlng_id is null; > +------+----------+ > | id | chlng_id | > +------+----------+ > | 2 | NULL | > | 4 | NULL | > | 5 | NULL | > +------+----------+ > > PB > > > > > >
