Web lists-archives.com

Re: Trouble with LEFT JOIN




On 2015-09-04 11:39 AM, Richard Reina wrote:

2015-09-04 11:18 GMT-05:00 Peter Brawley <peter.brawley@xxxxxxxxxxxxx <mailto:peter.brawley@xxxxxxxxxxxxx>>:

    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