Web lists-archives.com

Re: Trouble with LEFT JOIN




2015-09-04 11:18 GMT-05:00 Peter Brawley <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
>
> -----
>
>
> 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 );