On 2015-09-04 11:39 AM, Richard Reina wrote:
2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.braw...@earthlink.net
<mailto: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
