Web lists-archives.com

RE: Trouble with LEFT JOIN




Hi There,

If I interpreted what you are trying to do is to return all the rows from Challenge and reference Patrocinio excluding those rows in Patrocinio where PTRN_ID is 1?

Not sure if the below is possible in MySQL but I've used this in other places when doing a left join and needing to exclude specific rows when satisfying the join condition.

select ...
from table_1 t1 
     left join table_2 t2 on t1.id = t2.id and not t2.fld = 1
where ...

Apologies I don't have a MySQL instance to test the above and it's been quite a while since I've dabbled seriously with this database.

HTH

Cheers.
AB


-----Original Message-----
From: Richard Reina [mailto:gatorreina@xxxxxxxxx] 
Sent: Saturday, 26 September 2015 1:09 AM
To: peter.brawley@xxxxxxxxxxxxx
Cc: mysql@xxxxxxxxxxxxxxx
Subject: Re: Trouble with LEFT JOIN

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.brawley@xxxxxxxxxxxxx>:

> On 2015-09-04 11:39 AM, Richard Reina wrote:
>
>
> 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
>>
>> -----
>>
>>
>
> 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
>
>
>
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql